When working with data in SQL Server, you may often need to find the median value. The median is the value that separates the higher half of a data sample from the lower half. In other words, it is the middle value of a dataset.

Unfortunately, SQL Server does not have a built-in function to directly calculate the median. However, there is a workaround that you can use if you are running SQL Server 2012 or 2014.

In 2012, Microsoft introduced a new function called PERCENTILE_CONT. This function allows you to find the value ranked at a specific percentile. By supplying a percentile of 0.5 (which represents 50%), you can effectively find the median value.

Let’s take a look at a simple example:

USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty, ProductID,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ProductID)
OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC
GO

The above query will return the median value for each SalesOrderID in the specified dataset.

It’s important to note that if there is not an exact value found at the rank, PERCENTILE_CONT will interpolate the answer instead. This means that it will estimate the median value based on the surrounding data points.

In order to understand how PERCENTILE_CONT works, let’s take a closer look at the definition of the median:

• In case of an even number of elements: In an ordered list, add the two digits from the middle and divide by 2.
• In case of an odd number of elements: In an ordered list, select the digit from the middle.

By using the PERCENTILE_CONT function, we can easily find the median value in SQL Server.

For more information on T-SQL median and the PERCENTILE_CONT function, you can refer to the following resources:

• SQL SERVER – What are T-SQL Median? – Notes from the Field #090
• SQL SERVER – Introduction to PERCENTILE_CONT ( ) – Analytic Functions Introduced in SQL Server 2012

With the PERCENTILE_CONT function, you can now easily find the median value in SQL Server and perform various calculations and analysis on your data.