Calculate Median in SQL Server

Median is the numeric value that separates higher half of the list from the lower half. If the list contains odd number of values, median is the number available at the position
(n+1)/2 when numbers are arranged in ascending order. Otherwise it is the average of the numbers at position (n+1)/2 and (n+2)/2. Let us see both these scenarios with examples

Scenario 1: Odd number of values

Create a testing table variable with sample data

declare @table table(number int)
insert into @table
select 3 union all
select 19 union all
select 6 union all
select 1 union all
select 48

--Find number of items in the list
declare @n int
select @n=COUNT(*) from @table

if @n%2=1
select number as median from
(
select ROW_NUMBER() over (order by number) as sno,* from @table
) as t
where sno=(@n+1)/2
else
select avg(number*1.0) as median from
(
select ROW_NUMBER() over (order by number) as sno,* from @table
) as t
where sno in ((@n+1)/2,(@n+2)/2)

OUTPUT

Scenario 2: Even Number of values

OUTPUT

Row number function is used to assign a serial number based on ascending order of the list. The condition if @n%2=1 checks if the total number of list is odd number and (@n+1)/2 will be the number which indicates the position of the median number and the query takes that number.

If @n%2<>1, the query takes the average of the numbers in the positions (@n+1)/2,(@n+2)/2

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan