Calculate Median in SQL Server

In this post, I am sharing how to calculate the median in SQL Server.

Let's first create a table and populate it with some data

create table tblData (val int)
go
insert into tblData
select 10 union all
select  8 union all
select 14 union all
select 20 union all
select 11 union all
select 18 union all
select 21

In the table we have following list of values

• 8, 10, 11, 14, 18, 20, 21

Here, the no. of values is 7 (odd), so median value is (7+1)/2 = 4th value i.e 14

Now, lets run the script to calculate median

In the below example, we have used Row_Number.

Refer related post to understand in detail:  Row_Number in SQL Server

declare @cnt int
select @cnt = count(1) from tblData
;with cte as(
    select  val, ROW_NUMBER() over (order by val) as rid
    from    tblData
)
select  cast(AVG(val*1.0) as numeric(10,2)) as median
from    cte   
where   rid in ((@cnt+1)/2, (@cnt+2)/2)

OUTPUT

median
---------------------------------------
14.00
(1 row(s) affected)

Now, Let's add another value in the table

insert into tblData values(21)

Now, In the table we have following list of values

• 8, 10, 11, 14, 18, 20, 21, 21

Now, the no. of values is 8 (even), so median value is (8/2 + (8/2+1))/2
= (4th value + 5th value)/2 = 14 + 18 = 16

Let's run the script again to calculate median

declare @cnt int
select @cnt = count(1) from tblData
;with cte as(
    select  val, ROW_NUMBER() over (order by val) as rid
    from    tblData
)
select  cast(AVG(val*1.0) as numeric(10,2)) as median
from    cte   
where   rid in ((@cnt+1)/2, (@cnt+2)/2)

OUTPUT

median
---------------------------------------
16.00

(1 row(s) affected)