# How to Find Median in SQL Server? – Interview Question of the Week #116

Question: How to Find Median in SQL Server?

Answer: Before we see the answer, let us first see the definition of median.

Defination of Median as per Wikipedia: The median is the value separating the higher half of a data sample, a population, or a probability distribution, from the lower half. In simple terms, it may be thought of as the “middle” value of a data set.

There is no MEDIAN function in T-SQL. If, however, you are running SQL Server 2012 or 2014, there is an easy workaround.  In 2012, Microsoft introduced a new function called PERCENTILE_CONT. Given a percent rank and a set of values, PERCENTILE_CONT will return the value ranked at that percent. If there is not an exact value found at the rank, PERCENTILE_CONT will interpolate the answer instead. If you supply 0.5, meaning 50%, PERCENTILE_CONT will return the median value.

Let us see 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 give us the following result:

You can see that I have used PERCENTILE_COUNT (0.5) in the query, which is similar to finding median. Let me explain above diagram with little more explanation. The definition of median is as following:

In case of Even Number of elements = In order list, add the two digits from the middle and divide by 2
In case of Odd Numbers of elements = In order list, select the digits from the middle

I hope this example gives clear idea how PERCENTILE_CONT () works and help us find the median.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

## Activity Monitor and Running Query – SQL in Sixty Seconds #115

• Thank you very much for the content. I learned something new.