?
Solved

SQL QUERY Quartile

Posted on 2011-03-01
3
Medium Priority
?
815 Views
Last Modified: 2012-05-11
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
Comment
Question by:flickimp1717
3 Comments
 
LVL 19

Expert Comment

by:Rimvis
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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
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

Open in new window

0
 
LVL 22

Accepted Solution

by:
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
*/

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question