Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

SQL QUERY Quartile

Posted on 2011-03-01
Medium Priority
799 Views
SQL Table

Col1    Col 2    Col 3    Col 4   etc,,,,,
Tim     134       45        78
Dan     140      65        90
Jane    128     59        87
Imagine there are 100 rows of this...

Etc

What I want is the Lower Quartile for Col 2, Col 3, etc....
Simple process is do it in Excel, but I want to automate it in SQL

Any ideas?
0
Question by:flickimp1717
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 19

Expert Comment

ID: 35014763
Hi flickimp1717,

try this:
``````SELECT (SELECT MAX(Col2) FROM (SELECT TOP 25 PERCENT Col2 FROM YourTable ORDER BY Col2) AS c2) AS Col2,
(SELECT MAX(Col3) FROM (SELECT TOP 25 PERCENT Col3 FROM YourTable ORDER BY Col3) AS c3) AS Col3,
(SELECT MAX(Col4) FROM (SELECT TOP 25 PERCENT Col4 FROM YourTable ORDER BY Col4) AS c3) AS Col4
``````
0

LVL 41

Expert Comment

ID: 35014767
Are you looking for something like this?
``````SELECT MIN(col1) AS Min_Col1,
MIN(col2) AS Min_Col2,
MIN(col3) AS Min_Col3,
MIN(col4) AS Min_Col4,
MIN(col5) AS Min_Col5
FROM table1
``````
0

LVL 22

Accepted Solution

Thomasian earned 2000 total points
ID: 35014906
This query will return the same result as the quartile function of excel
``````declare @t table (col2 int, col3 int)
insert @t
SELECT 200,423 UNION ALL
SELECT 128,534 UNION ALL
SELECT 135,643 UNION ALL
SELECT 170,467 UNION ALL
SELECT 301,958 UNION ALL
SELECT 131,711

;WITH CTE1 AS
(
SELECT col2
,col3
,(COUNT(1) OVER () -1)/4.0 + 1 qrt
,ROW_NUMBER() OVER (ORDER BY col2) rn2
,ROW_NUMBER() OVER (ORDER BY col3) rn3
FROM @t
)
,CTE2 AS
(
SELECT qrt - FLOOR(qrt) d
,MIN(CASE WHEN rn2 BETWEEN FLOOR(qrt) AND CEILING(qrt) THEN col2 END) col2Low
,MAX(CASE WHEN rn2 BETWEEN FLOOR(qrt) AND CEILING(qrt) THEN col2 END) col2High
,MIN(CASE WHEN rn3 BETWEEN FLOOR(qrt) AND CEILING(qrt) THEN col3 END) col3Low
,MAX(CASE WHEN rn3 BETWEEN FLOOR(qrt) AND CEILING(qrt) THEN col3 END) col3High
FROM CTE1
GROUP BY qrt
)
SELECT col2low + (col2High - col2Low) * d QrtCol2
,col3low + (col3High - col3Low) * d QrtCol3
FROM CTE2
/* Result
QrtCol2        QrtCol3
132.000000     483.750000
*/
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month8 days, 4 hours left to enroll