Solved

SQL QUERY Quartile

Posted on 2011-03-01
3
754 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 40

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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 39
Is this spec enough for a developer or is it just blabble ? 1 35
SQL R 21 22
Getting invalid Syntax SQL. 3 14
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

766 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