Get Median value from SQL Server Table

Sample code to get the median of table data in SQL Server. Declare two variables first to get the number of rows and second to store median value.
DECLARE @Count int,@Median Float

Query to get the number of rows:
SELECT @Count=COUNT(*) FROM (
SELECT ID,COUNT(ID) AS IDCount FROM MyTable GROUP BY ID)AB

if the number of rows are even then query to get the median value is:
SELECT @Median=(SUM(Convert(float,IDCount))/2) FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row IN ((@Count/2),(@Count/2)+1)

Query to get the median in case of odd rows:
SELECT @Median=IDCount FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row =((@Count/2)+1)

Sample Query:
DECLARE @Count int,@Median Float
SELECT @Count=COUNT(*) FROM (
SELECT ID,COUNT(ID) AS IDCount FROM MyTable GROUP BY ID)AB

DECLARE @SQLQuery varchar(500)
IF(@Count%2=0)
BEGIN
SELECT @Median=(SUM(Convert(float,IDCount))/2) FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row IN ((@Count/2),(@Count/2)+1)
END
ELSE
SELECT @Median=IDCount FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row =((@Count/2)+1)
SELECT @Median

3 comments:

Jon Deny said...
This comment has been removed by the author.
Jon Deny said...
This comment has been removed by the author.
Lakhan Pal Garg said...

What is yourQuery Jon Can you please write your commnets properly.