Function to Calculate Median in SQL Server

Question

According to MSDN, Transact-SQL does not support Median as an aggregate function. However, I'd like to know if it's feasible to develop this capability (using the Create Aggregate function, user defined function, or some other method).

What would be the best approach to accomplish this—to permit the computation of a median value in an aggregate query, assuming a numeric data type—if it were possible?

Answer 1

Use a simple-ish median calculation for a single column in a table:

SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median