How to calculate median in SQL Server?

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 median using the 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

Related Articles

• Read about calculating statistical Mean In SQL Server.
• Read about calculating Mode In SQL Server.

Reference

• More about PERCENTILE_CONT at Microsoft Docs.