Calculate or Find median group by in SQL Server

Description:

In previous example we already explain that how to calculate median in SQL Server. But in this example we explain that function to find median group by in SQL Server. Or calculate median group by in SQL Server. Before we calculate/find median in sql server first we understand what the median is and formula to calculate the median in sql server.

What is Median?

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it.

For ex: supposed to find the median of bellows values like

{1,2,3,4,5} then the median is equal 3

{1,2,5,10} then the median is equal 3.5

{1,5,8,7} then median is equal 6

So how to calculate median group by  in SQL Server query, below is the query to find the median

Query:

SELECT

r.Name,

AVG(CONVERT(FLOAT, r.Score)) as median

FROM

(SELECT

ROW_NUMBER() OVER (PARTITION BY s.Name ORDER BY s.Score) AS RecordNumber,

COUNT(*) OVER (PARTITION BY s.Name) AS NameRecordCount,

CAST((COUNT(*) OVER (PARTITION BY s.Name)) AS DECIMAL(9, 1)) / 2.0 AS MedianPoint,

COUNT(*) OVER (PARTITION BY s.Name) % 2 AS IsOdd,

s.Name,

s.Score

FROM

Demo AS s

) AS r

WHERE

(r.IsOdd = 1

AND r.RecordNumber = CEILING(MedianPoint)

)

OR

(r.IsOdd = 0

AND r.RecordNumber IN (MedianPoint, MedianPoint + 1)

)

GROUP BY

r.Name