Calculate the Median Value in SQL Server

use testbase

create table tblMedian(val int)
Insert into tblMedian (val) Values (10)
Insert into tblMedian (val) Values (30)
Insert into tblMedian (val) Values (60)
Insert into tblMedian (val) Values (100)
Insert into tblMedian (val) Values (110)
Insert into tblMedian (val) Values (150)
Insert into tblMedian (val) Values (170)
Insert into tblMedian (val) Values (1000)

select * from tblMedian ORDER BY val ASC

Via I :
DECLARE @M1 int, @M2 int
SELECT TOP 50 PERCENT @M1 = val FROM tblMedian ORDER BY val ASC
SELECT TOP 50 PERCENT @M2 = val FROM tblMedian ORDER BY val DESC
SELECT (@M1+@M2)/2.0

Results :
MedianValue
—————————————
105.000000

Via II :
SELECT (
(SELECT TOP 1 val FROM
(SELECT TOP 50 PERCENT val FROM tblMedian WHERE val IS NOT NULL ORDER BY val) AS A ORDER BY val DESC)
+
(SELECT TOP 1 val FROM
(SELECT TOP 50 PERCENT val FROM tblMedian WHERE val IS NOT NULL ORDER BY val DESC) AS A ORDER BY val ASC)
) / 2 as MedianValue

Results:
MedianValue
—————-
105

Tags: calculate median, Calculate the Median Value in SQL Server, find median, Median, Median Value, Median value in sql server, SQL Server