# Calculating Median Value within a Group By

I have a SPROC which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is calculate the Median value... I suspect I would need a subquery but can't figure out how to accomplish this.

``````SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time]
,AVG(CASE WHEN Duration is null OR Duration = 0 THEN null ELSE Duration END) AS [Mean Response Time]
FROM [Application].[Exchange] WITH (NOLOCK)
WHERE [Started] >= '24 Oct 2012' AND [Started] < '25 Oct 2012'
GROUP BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)
ORDER BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)
``````

Can anyone suggest a way I might accomplish this?

-
CLR aggregate function. Which version of SQL Server are you using? – Jon Seigel Oct 25 '12 at 14:06
SQL Server 2008 and SQL Azure – JoeGeeky Oct 25 '12 at 14:13
Two standard methods due to Itzik Ben-Gan and Joe Celko are shown in this blog post by Adam Machanic. – Paul White Oct 26 '12 at 13:33

``````SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- replace WITH (nolock)

SELECT [Time]
,AVG(NULLIF(Duration,0)) AS [Mean Response Time]
,AVG(CASE WHEN Rn in (Round(CN/2.0,0),Round((CN+1)/2.0,0))
THEN Duration END) [Median]
FROM
(
SELECT [Time], Duration,
Rn = Row_Number() over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
Time
order by Duration),
Cn = Count(1) over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
Time)
FROM (
SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time],
Duration
FROM [Application].[Exchange]
WHERE [Started] >= '20121024' AND [Started] < '20121025'
) X
) Y
GROUP BY [Time]
ORDER BY [Time];
``````
-
+1 but your answer will be more useful if you use yyyymmdd date literals such as '20121024' instead of '24 Oct 2012', because they will work for non-English speaking locales. – A-K Oct 25 '12 at 14:33