Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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

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.

 

Print | posted on Monday, August 30, 2010 12:00 AM | Filed Under [ T-SQL Techniques SQL Server 2005 GROUP BY ]

Feedback

Gravatar

# re: How to calculate Median in SQL Server

You can avoid some unnecessary calculations in the IN part by using this

weblogs.sqlteam.com/...

8/30/2010 8:19 AM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET