Median values in T-SQL

Question

For even rows, the formula for median is (104.5 + 108)/2 for the 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

I wrote this query, and it is calculating the correct median when the number of rows are odd:

WITH    a AS ( SELECT   Total ,
                        ROW_NUMBER() OVER ( ORDER BY CAST(Total AS FLOAT) ASC ) rownumber
               FROM     [Table] A
             ),
        b AS ( SELECT TOP 2
                        Total ,
                        isodd
               FROM     ( SELECT TOP 50 PERCENT
                                    Total ,
                                    rownumber % 2 isodd
                          FROM      a
                          ORDER BY  CAST(Total AS FLOAT) ASC
                        ) a
               ORDER BY CAST(total AS FLOAT) DESC
             )
    SELECT  *
    FROM    b

What is the general T-SQL query to find the median in both situations? Like when the number of rows are odd and also when the number of rows is even?

Could my query be twisted so that it can work for the median in both even and odd number of rows situations?

Answer 1

I wrote a blog about Mean, Median and Mode a couple years ago. I encourage you to read it.

Calculating Mean, Median, and Mode with SQL Server

SELECT ((
        SELECT TOP 1 Total
        FROM   (
                SELECT  TOP 50 PERCENT Total
                FROM    [TABLE] A
                WHERE   Total IS NOT NULL
                ORDER BY Total
                ) AS A
        ORDER BY Total DESC) +
        (
        SELECT TOP 1 Total
        FROM   (
                SELECT  TOP 50 PERCENT Total
                FROM    [TABLE] A
                WHERE   Total IS NOT NULL
                ORDER BY Total DESC
                ) AS A
        ORDER BY Total ASC)) / 2