HOW TO CALCULATE MEDIAN IN SQL SERVER

-- sample table:

create table Employee

(

Name varchar(1) primary key,

City varchar(10),

Age int

)

go

-- with some sample data:

insert into Employee

select 'A','Calcutta',13 union all -- odd #

select 'B','Calcutta',33 union all

select 'C','Calcutta',19 union all

select 'D','Delhi',25 union all -- single #

select 'E','Mumbai',22 union all -- even #

select 'F','Mumbai',65 union all

select 'G','Mumbai',67 union all

select 'H','Mumbai',71

go

-- here's our query, showing median age per city:

select city,

AVG(age) as MedianAge

from

(

select City, Name, Age,

ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,

COUNT(*) over (partition by City) as CityCount

from

Employee

) x

where

x.AgeRank in (x.CityCount/2+1, (x.CityCount+1)/2)

group by

x.City

go

-- clean it all up

drop table Employee

And here's the result:

city MedianAge

---------- -----------

Calcutta 19

Delhi 25

Mumbai 66

(3 row(s) affected)

Simply remove "City" from the SELECT clause and the GROUP BY clause to get the median age for all.

There may be more efficient tricks out there, but this is certainly the shortest and simplest technique I am aware of.