In SQL Server, there is no direct way yet to find the median of a range of numbers. However, developers used several work-arounds to get the median using several techniques like MIN MAX, AVG, ect… In SQL Server 2012, Microsoft introduced an analytic function PERCENTILE_CONT function. Here is one another simple way to calculate the median PERCENTILE_CONT function.
For this illustration, I have used the table [Sales].[OrderLines] from Microsoft’s sample database WideWorldImporters. To get the median we have to use PERCENTILE_CONT(0.5). If you want to define a specific set of rows grouped to get the median, then use the OVER (PARTITION BY) clause. Here I’ve used PARTITION BY on the column OrderID so as to find the median of unit prices for the order ids.
Sample Query To Calculate Median
SELECT OrderID, StockItemID, UnitPrice, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY OrderID) AS Median_UnitPrice FROM [Sales].[OrderLines] ORDER BY OrderID, UnitPrice; GO
- More about PERCENTILE_CONT at Microsoft Docs.