Calculate Median in SQL Server

-- Just copy & paste

declare @People table
(
Person varchar(5),
City varchar(10),
Age int
)

insert into @People(Person,City,Age)
select 'P1','Delhi',23 union all -- odd #
select 'P2','Delhi',43 union all
select 'P3','Delhi',29 union all

select 'P4','Bombay',15 union all -- single #

select 'P5','Kolkata',12 union all -- even #
select 'P6','Kolkata',55 union all
select 'P7','Kolkata',57 union all
select 'P8','Kolkata',61

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
) T
where
T.AgeRank in (T.CityCount/2+1, (T.CityCount+1)/2)
group by
T.City