How to calculate Median in SQL Server
Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him.
-- sample table:
create table People
(
Person varchar(1) primary key,
City varchar(10),
Age int
)
go
-- with some sample data:
insert into People
select 'A','Boston',23 union all -- odd #
select 'B','Boston',43 union all
select 'C','Boston',29 union all
select 'D','Chicago',15 union all -- single #
select 'E','NY',12 union all -- even #
select 'F','NY',55 union all
select 'G','NY',57 union all
select 'H','NY',61
go
-- here's our query, showing median age per city:
select city,
AVG(age) as MedianAge
from
(
select City, Person, Age,
ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,
COUNT(*) over (partition by City) as CityCount
from
People
) x
where
x.AgeRank in (x.CityCount/2+1, (x.CityCount+1)/2)
group by
x.City
go
-- clean it all up
drop table People
create table People
(
Person varchar(1) primary key,
City varchar(10),
Age int
)
go
-- with some sample data:
insert into People
select 'A','Boston',23 union all -- odd #
select 'B','Boston',43 union all
select 'C','Boston',29 union all
select 'D','Chicago',15 union all -- single #
select 'E','NY',12 union all -- even #
select 'F','NY',55 union all
select 'G','NY',57 union all
select 'H','NY',61
go
-- here's our query, showing median age per city:
select city,
AVG(age) as MedianAge
from
(
select City, Person, Age,
ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,
COUNT(*) over (partition by City) as CityCount
from
People
) x
where
x.AgeRank in (x.CityCount/2+1, (x.CityCount+1)/2)
group by
x.City
go
-- clean it all up
drop table People
And here's the result:
city MedianAge
---------- -----------
Boston 29
Chicago 15
NY 56
(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.
Legacy Comments
|
Peso
2010-08-30 |
re: How to calculate Median in SQL Server You can avoid some unnecessary calculations in the IN part by using this weblogs.sqlteam.com/... |