# How to Calculate Median?

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
WHERE     (Quizzes.PartnerID = 16)

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

DROP TABLE #QuizTemp

The average works great and now I need the Median.

13.10.2009 17:25:09
2 ОТВЕТА
РЕШЕНИЕ

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
WHERE     (Quizzes.PartnerID = 16)

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
(
, ROW_NUMBER() OVER (ORDER BY TotalScore) AS RowNumber
WHERE     (Quizzes.PartnerID = 16)
)

it should work for your query

13.10.2009 18:08:31

Median value of numeric values is often over though. Just use this example:

DECLARE @testTable TABLE
(
VALUE   INT
)
--INSERT INTO @testTable -- Even Test
--SELECT 3 UNION ALL
--SELECT 5 UNION ALL
--SELECT 7 UNION ALL
--SELECT 12 UNION ALL
--SELECT 13 UNION ALL
--SELECT 14 UNION ALL
--SELECT 21 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 29 UNION ALL
--SELECT 40 UNION ALL
--SELECT 56

--
--INSERT INTO @testTable -- Odd Test
--SELECT 3 UNION ALL
--SELECT 5 UNION ALL
--SELECT 7 UNION ALL
--SELECT 12 UNION ALL
--SELECT 13 UNION ALL
--SELECT 14 UNION ALL
--SELECT 21 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 29 UNION ALL
--SELECT 39 UNION ALL
--SELECT 40 UNION ALL
--SELECT 56

DECLARE @RowAsc TABLE
(
ID      INT IDENTITY,
Amount  INT
)

INSERT INTO @RowAsc
SELECT  VALUE
FROM    @testTable
ORDER BY VALUE ASC

SELECT  AVG(amount)
FROM @RowAsc ra
WHERE ra.id IN
(
SELECT  ID
FROM    @RowAsc
WHERE   ra.id -
(
SELECT  MAX(id) / 2.0
FROM    @RowAsc
) BETWEEN 0 AND 1

)
13.06.2011 13:07:23