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 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
Calculate Median In SQL Server

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.