Calculating median has never been an issue, as we have lots of solutions to calculate medians using CTE, user defined functions etc. But what encourages me to write this post is, I found a shortcut to calculate the median using PERCENTILE_CONT (A new analytic function shipped in SQL Server 2012).
As per the definition : To find the Median, place the numbers you are given in value order and find the middle number. Click here for Reference
Given below is the sample that I will use to demonstrate what is median.
<br />SELECT ProductID,StandardCost FROM<br />[Production].[ProductCostHistory]<br />WHERE ProductID IN (707,708,711,712,713)<br />ORDER BY ProductID<br />GO<br />--OUTPUT<br />
Given below is the script to calculate the median in a shortest way.
<br />Use AdventureWorks2012<br />GO<br />SELECT<br />DISTINCT [ProductID]<br />,PERCENTILE_CONT(0.5)<br />WITHIN GROUP (ORDER BY [StandardCost])<br />OVER (PARTITION BY [ProductID]) AS Median<br />FROM [Production].[ProductCostHistory]<br />WHERE ProductID IN (707,708,711,712,713)<br />ORDER BY [ProductID]<br />GO<br />--OUTPUT<br />
Note : Whenever you calculate median using PERCENTILE_CONT make sure it is PERCENTILE_CONT(0.5).
SQL Server Portal 