Calculate median of values in T-SQL

I've got a piece of code (stored procedure) written in T-SQL that inserts into a table several data from a temporal table as shown here:

``````INSERT INTO [myschema].[CalculatedData](
Year,
Month,
Line,
Car,
Service,
Route,
Trip,
Stop,
Qty,
CalculatedMean
) SELECT
Year,
Month,
Line,
Car,
Service,
Route,
Trip,
Stop,
COUNT(*),
AVG(Duration),
FROM
@TableToCalculate
GROUP BY
Year,
Month,
Line,
Car,
Service,
Route,
Trip,
Stop;
``````

As you can see, I calculate the average (mean) of all the values. Easy, considering the built-in function.

Now, what I want to do is to calculate the median. There isn't any function to calculate it, but as the median is equal to calculating the 50th percentile, this can be used. But I don't know how to do it.

• Have you considered implementing the aggregate function in CLR? Commented Jul 5, 2022 at 22:14

You can find a survey and performance comparison of the main SQL Server methods in Aaron Bertrand's articles Best approaches for grouped median and What is the fastest way to calculate the median?

With suitable indexing, the method proposed by Peter Larsson often performs best, though the logic is a little trickier to follow. The following example is quoted from Aaron's first article, using a sales data set:

``````SELECT d.SalesPerson, w.Median
FROM
(
SELECT SalesPerson, COUNT(*) AS y
FROM dbo.Sales
GROUP BY SalesPerson
) AS d
CROSS APPLY
(
SELECT AVG(0E + Amount)
FROM
(
SELECT z.Amount
FROM dbo.Sales AS z
WHERE z.SalesPerson = d.SalesPerson
ORDER BY z.Amount
OFFSET (d.y - 1) / 2 ROWS
FETCH NEXT 2 - d.y % 2 ROWS ONLY
) AS f
) AS w(Median);
``````

You might also be interested in reading my article about Calculating the Median with a Dynamic Cursor, which performs surprisingly well. I also have Improving the Row Numbering Median Solution showing how to improve the performance of Peter's solution further.

Related Q & A here:

You may try:

``````PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY Year,Month) OVER (PARTITION BY Duration) AS Median
``````

As in Microsoft documentation, the PERCENTILE_DISC() function:

Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions in SQL Server. For a given percentile value P, PERCENTILE_DISC sorts the expression values in the ORDER BY clause.

For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values. The result is equal to a specific column value.

• It is better if the answer is obvious. As it stands, viewers have to try it. Please explain the various parts. Commented Nov 14, 2023 at 20:30