Solved

SQL QUERY Quartile

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now