Thursday, July 14, 2011

Tech Qu: Find the Median/Percentile value of a table

Everyone knows how to find the average of a column, asking to find a median (technically the 50percentile) in TSQL  can be an exercise for some. A simple solution is shown below.

 

Code Snippet
  1. DECLARE @Percentile int=50
  2. DECLARE @PercentileTop int
  3. DECLARE @PercentileValue money
  4. SELECT @PercentileTop =count(1)
  5.   FROM [SalesLT].[Product]
  6.   WHERE LISTPRICE IS NOT NULL
  7. SET @PercentileTop =(@PercentileTop * @Percentile)/100
  8.   SELECT TOP(@PercentileTop)@PercentileValue=[ListPrice]      
  9.   FROM [SalesLT].[Product]
  10.   WHERE LISTPRICE IS NOT NULL
  11.   ORDER BY LISTPRICE
  12. SELECT @PercentileValue

 

The next question would be to encapsulate this for reuse – I will leave that for people to comment on. Let us see how creative our readers can be!

6 comments: