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!

4 comments:

  1. It was a awe-inspiring post and it has a significant meaning and thanks for sharing the information.Would love to read your next post too......

    Thanks

    Regards

    Best B Schools in india

    ReplyDelete
  2. putting lectures on iPods/iPhones and she was fascinated. I promised to send her details about how to do this.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete