Sunday, September 25, 2011

Calculating PERCENTILE in SQL Server

How to find 25th percentile or 75th percentile of given set of numbers (records in table) in SQL Server 2005?

In this post I will show couple of ways of calculating Percentile. I am not a statistician so i just took the help of YouTube to learn on how Percentile is actually calculated. During that course I found two different formulas for calculating Percentile! So I have provided couple of methods to calculate Percentile based on both the formulas I learnt.


IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[tblPercentile]') AND type in (N'U'))
DROP TABLE [dbo].[tblPercentile]
GO


CREATE TABLE tblPercentile
(
Sno INT identity(1,1), iValue float
)
GO

--First set of sample records
Insert into tblPercentile
SELECT 2 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 5 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 8 UNION ALL SELECT 8 UNION ALL
SELECT 8 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 11 UNION ALL SELECT 12

--Second dataset for testing
--Insert into tblPercentile
-- SELECT 22 UNION ALL SELECT 22 UNION ALL
-- SELECT 23 UNION ALL SELECT 24 UNION ALL
-- SELECT 24 UNION ALL SELECT 24 UNION ALL
-- SELECT 25 UNION ALL SELECT 27 UNION ALL
-- SELECT 27 UNION ALL SELECT 28 UNION ALL
-- SELECT 30 UNION ALL SELECT 33
GO

--Solution:
/*
Method 1:
Reference URL - http://www.youtube.com/watch?v=Snf6Wpn-L4c&NR=1
*/


--Variable declaration
DECLARE @percentile int
DECLARE @tmp float

--Assign the PERCENTILE value which you want to calculate
Set @percentile = 25


SELECT 
@tmp = @percentile/100.0 * (COUNT(*)+1) 
FROM tblPercentile


SELECT 
CASE WHEN COUNT(ivalue) > 1 THEN SUM(ivalue)/2.0 
    ELSE MAX(iValue) 
END AS [Percentile] 
FROM tblPercentile
WHERE Sno BETWEEN FLOOR(@tmp) AND CEILING(@tmp)

/* 
Method 2:
Reference URL - http://www.thefreemathtutor.com/thepercentileformula.html
*/

SELECT iValue AS [Percentile] FROM tblPercentile
WHERE sno = (
SELECT CEILING(@percentile/100.0 * (COUNT(*))) 
FROM tblPercentile
)

No comments: