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:
What is yourQuery Jon Can you please write your commnets properly.
Post a Comment