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.
Here are some additional 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
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
