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.

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

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:

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

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

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

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

Here are some additional resources:

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

, ,
Previous Post
What is the Difference between SUSPECT and RECOVERY PENDING? – Interview Question of the Week #114
Next Post
How to Change Owner of Database in SQL SERVER? – Interview Question of the Week #117

Related Posts

5 Comments. Leave new

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

    Reply
  • Great article! Thanks a lot. I do have a question though: how do you implement the logic of even and odd numbers in the SQL Query? Or is that already taken care of by using the PERCENTILE_COUNT?

    Reply
  • Exactly what I wanted. My boss said GET ME THE MEDIAN PRODUCT ID BY LUNCH TIME! Now I have it.

    Reply
  • If someone asked me that question on an interview, I would think considerably less about my interviewers. Unless you’re working with statistics every day, this is definitely in the category of “Let me Google that for you”… look it up when you need it, otherwise just remember that there is no MEDIAN aggregate function in TSQL (for some bizarre reason)…

    Reply

Leave a Reply

Menu