Median in SQL Server 2000

Question

For even rows formula for median is (104.5 + 108)/2 for table below and For odd rows it is 108 for table below

Total       Total

100         100
101         101
104.5       104.5
108         108
108.3       108.3
112         112
            114

Code below works in SQL Server 2008 but not in SQL Server 2000 as it does not understand row_number() and over.

How can we change the lower code to make it work on SQL Server 2000?

select avg(Total) median from
(select Total, 
rnasc = row_number() over(order by Total),
rndesc = row_number() over(order by Total desc)
 from [Table] 
) b
where rnasc between rndesc - 1 and rndesc + 1

score 3 · Answer 1 · edited May 23 '17 at 11:43

3

If you only want a median, you may use this simple query.

SELECT
(
  (SELECT MAX(Total) FROM
    (SELECT TOP 50 PERCENT Total FROM [Table] ORDER BY Total) AS BottomHalf)
  +
  (SELECT MIN(Total) FROM
    (SELECT TOP 50 PERCENT Total FROM [Table] ORDER BY Total DESC) AS TopHalf)
) / 2.0 AS Median

Source: Function to Calculate Median in Sql Server

edited May 23 '17 at 11:43

Community

1
1

answered Sep 05 '11 at 16:10

NullCoder

327
5
15

You mean SELECT TOP 50 PERCENT **Total** not Score, right? Other than that, I think this is really clever. – deutschZuid Sep 06 '11 at 03:17
1

And the MAX ones :P.. Another thing you should probably have / 2.0 instead of just with the integer 2 just in case all operands involved in the maths are integers. – deutschZuid Sep 06 '11 at 03:25

score 1 · Answer 2 · answered Sep 05 '11 at 16:08

SELECT Median = AVG(Total) FROM 
( 
    SELECT Total FROM ( 
        SELECT TOP 1 Total = Total * 1.0 FROM 
        ( 
            SELECT TOP 50 PERCENT Total 
            FROM dbo.[Table] ORDER BY Total 
        ) AS sub_a 
        ORDER BY 1 DESC 
    ) AS sub_1 
    UNION ALL 
    SELECT Total FROM ( 
        SELECT TOP 1 Total = Total * 1.0 FROM 
        ( 
            SELECT TOP 50 PERCENT Total 
            FROM dbo.[Table] ORDER BY Total DESC 
        ) AS sub_b 
        ORDER BY 1 
    ) AS sub_2 
) AS median;

Median in SQL Server 2000

2 Answers2

Linked