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!

Comments

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

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

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

    ReplyDelete
  4. Thanks for sharing this code! I've used all discounts by Edusson and have no other possibility to hire someone to help me with coding.

    ReplyDelete

Post a Comment

Popular posts from this blog

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

Yet once more into the breech (of altered programming logic)

How to convert SVG data to a Png Image file Using InkScape