Interquartile Range - Lower, Upper and Median

Question

I'm trying to work out the interquartile range based on an array of numbers which can be any length e.g.

1,  1,  5,  6,  7,  8,  2,  4,  7,  9,  9,  9,  9

The values that I need to work out from this interquartile range are:

• Upper Quartile
• Median
• Lower Quartile

If I dump the above array of numbers into Microsoft Excel (columns A:M), then I can use the following formulas:

• =QUARTILE.INC(A1:M1,1)
• =QUARTILE.INC(A1:M1,2)
• =QUARTILE.INC(A1:M1,3)

To get my answers of:

• 4
• 7
• 9

I now need to work out these 3 values in either SQL Server or VB.NET. I can get the array values in any format or object in either of these languages, but I can't find any functions that exist like the QUARTILE.INC function that Excel has.

Does anyone know how this could be achieved in either SQL Server or VB.NET?

Answer 1

There might be an easier way, but to get Quartiles, you can use NTILE (Transact-SQL)

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

So for your data:

SELECT  1 Val
INTO    #temp
UNION ALL
SELECT  1
UNION ALL
SELECT  5
UNION ALL
SELECT  6
UNION ALL
SELECT  7
UNION ALL
SELECT  8
UNION ALL
SELECT  2
UNION ALL
SELECT  4
UNION ALL
SELECT  7
UNION ALL
SELECT  9
UNION ALL
SELECT  9
UNION ALL
SELECT  9
UNION ALL
SELECT  9

-- NTILE(4) specifies you require 4 partitions (quartiles)
SELECT  NTILE(4) OVER ( ORDER BY Val ) AS Quartile ,
        Val
INTO #tempQuartiles
FROM    #temp

SELECT * 
FROM #tempQuartiles

DROP TABLE #temp
DROP TABLE #tempQuartiles

This would produce:

Quartile    Val
1           1
1           1
1           2
1           4
2           5
2           6
2           7
3           7
3           8
3           9
4           9
4           9
4           9

From this you can work out what you're after.

So modifying the SELECT you can do this:

SELECT Quartile, MAX(Val) MaxVal
FROM #tempQuartiles
WHERE Quartile <= 3
GROUP BY Quartile

To produce:

Quartile    MaxVal
1           4
2           7
3           9