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

Question: How do you find the median in SQL Server?

Answer: First sort the values. With an odd count, the median is the middle value. With an even count, it is the average of the two middle values. SQL Server’s PERCENTILE_CONT(0.5) gives that interpolated middle value for a numeric set. The original example below uses sales order details, so you can see the answer for more than one group.

Find the median for each sales order

The original article used a sample database named AdventureWorks. This runnable version uses AdventureWorks2025 with the same SalesOrderDetail objects and order IDs. PERCENTILE_CONT is available from SQL Server 2012; its result is float(53) and NULL input values are ignored. PARTITION BY SalesOrderID gives each order its own median, and ORDER BY ProductID tells the function which values to place in order. The median appears on every detail row in that order because this is a window function.

USE AdventureWorks2025;
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, ProductID, SalesOrderDetailID;
GO

Here is the original result from that sample data. In order 43670, the two middle ProductID values are 710 and 773, so the median is 741.5. In order 43667, the middle values are 773 and 775, so the median is 774. The highlighted result is the useful part of this example, so it belongs beside the SQL.

Those values depend on the rows in the sample database version you run. If your AdventureWorks data differs, check the ordered ProductID values rather than expecting the same screenshot. Also notice the function name: PERCENTILE_CONT, not PERCENTILE_COUNT. For an even group it can interpolate between observed values, so 741.5 need not itself be a ProductID in the table.

That is the interview answer: define the middle first, partition by the group whose median you want, and use the result to explain why an even-sized set may produce a value between two rows. For more detail, see my earlier posts on T-SQL medians and PERCENTILE_CONT.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.