Solved

SQL QUERY Quartile

Posted on 2011-03-01
3
761 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
[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
  • Learn & ask questions
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 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 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