How to Calculate Median?

Question

I need to calculate a median on a set of data, so I created a temp table and have tried to follow some articles online with zero success, here is what I am working with:

CREATE TABLE #QuizTemp (QuizProfileID INT,Cnt INT,TotalScore INT)

INSERT INTO #QuizTemp
SELECT QuizAnswers.QuizProfileID, COUNT(QuizAnswers.QuizProfileID) AS Cnt, SUM(QuizAnswers.AnsweredYes) As TotalScore 
FROM         QuizAnswers INNER JOIN
                      Quizzes ON QuizAnswers.QuizID = Quizzes.QuizID
WHERE     (Quizzes.PartnerID = 16) 
GROUP BY QuizAnswers.QuizProfileID
HAVING COUNT(QuizAnswers.QuizProfileID)= 5

SELECT COUNT(*) AS CNT, Avg(TotalScore) AS AvgTotalScore  FROM #QuizTemp

DROP TABLE #QuizTemp

The average works great and now I need the Median.

Answer 1

try capturing the row count on INSERT and then select the row that is in the middle using ROW_NUMBER():

CREATE TABLE #QuizTemp (QuizProfileID INT,Cnt INT,TotalScore INT)
DECLARE @Rows int

INSERT INTO #QuizTemp
SELECT QuizAnswers.QuizProfileID, COUNT(QuizAnswers.QuizProfileID) AS Cnt, SUM(QuizAnswers.AnsweredYes) As TotalScore 
FROM         QuizAnswers INNER JOIN
                      Quizzes ON QuizAnswers.QuizID = Quizzes.QuizID
WHERE     (Quizzes.PartnerID = 16) 
GROUP BY QuizAnswers.QuizProfileID
HAVING COUNT(QuizAnswers.QuizProfileID)= 5


DECLARE @Rows int
SELECT @Rows=@@Rowcount

;with allrows as
(
    SELECT TotalScore, ROW_NUMBER() (ORDER BY TotalScore) AS RowNumber

)
SELECT @Rows AS CNT, TotalScore AS MedianScore
FROM allrows WHERE RowNumber=@Rows/2


DROP TABLE #QuizTemp

EDIT

Here is a solution without a temp table:

DECLARE @YourTable  table (TotalScore int)
INSERT INTO @YourTable Values (1)
INSERT INTO @YourTable Values (2)
INSERT INTO @YourTable Values (3)
INSERT INTO @YourTable Values (40)
INSERT INTO @YourTable Values (50)
INSERT INTO @YourTable Values (60)
INSERT INTO @YourTable Values (70)

;with allrows as
(
    SELECT
        TotalScore, ROW_NUMBER() OVER (ORDER BY TotalScore) AS RowNumber
        FROM @YourTable
)
,MaxRows AS
(SELECT MAX(RowNumber) AS CNT,CONVERT(int,ROUND(MAX(RowNumber)/2.0,0)) AS Middle FROM allrows)
SELECT 
    m.CNT
    ,(SELECT AVG(TotalScore) FROM allrows) AS AvgTotalScore  
    ,a.TotalScore AS Median
    ,m.Middle AS MedianRowNumber
    FROM allrows              a
        CROSS JOIN MaxRows    m
    WHERE a.RowNumber=m.Middle

OUTPUT:

CNT                  AvgTotalScore        Median      MedianRowNumber
-------------------- -------------------- ----------- --------------------
7                    32                   40          4

(1 row(s) affected)

if you edit the first CTE to be:

;with allrows as
(
    SELECT QuizAnswers.QuizProfileID, COUNT(QuizAnswers.QuizProfileID) AS Cnt, SUM(QuizAnswers.AnsweredYes) As TotalScore 
    , ROW_NUMBER() OVER (ORDER BY TotalScore) AS RowNumber
    FROM         QuizAnswers INNER JOIN
                          Quizzes ON QuizAnswers.QuizID = Quizzes.QuizID
    WHERE     (Quizzes.PartnerID = 16) 
    GROUP BY QuizAnswers.QuizProfileID
    HAVING COUNT(QuizAnswers.QuizProfileID)= 5
)

it should work for your query

Answer 2

This is a good article on implementing median in SQL Server.