Tag Archive for: median

When creating reports you may want to calculate median value from the subset of data. The most efficient way to do it is to use stored procedure. To get the median value we simply need to order the data by the sought value and take the number from the middle.

The code below does it’s job. When executing the script the in-memory subset of data is created, then it is being sorted and divided by 50%. Next we get the maximum value from 50% so it is the median value.

 declare @median decimal(18,2);
 with temp as
 ( 
     select dMonthIncomeNetto as salary from tblUserSalaryData 
 )
 SELECT @median = MAX(salary) FROM (SELECT TOP 50 PERCENT salary FROM temp ORDER BY salary) AS H1;

 select @median as median;

Calculating median value consumes quite a lot of server resources, so make sure you are calculating it when really needed. It is considered a good practice to store aggregated value of median and recalculate it periodically depending on your requirements.