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

August 2010 Blog Posts

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...

posted @ Monday, August 30, 2010 12:00 AM | Feedback (1) | Filed Under [ T-SQL Techniques SQL Server 2005 GROUP BY ]

Powered by:
Powered By Subtext Powered By ASP.NET