Get the Median Salary of Employees Query SQL Server

Get the Median Salary of Employees Query Using MS SQL Server?

Calculating the median salary in SQL Server involves a few steps, as SQL Server does not have a built-in median function. To find the median salary, you need to determine the middle value of the sorted salaries. If there is an even number of salaries, the median is the average of the two middle values.

Here’s a SQL query to compute the median salary of employees:

SQL Query

WITH OrderedSalaries AS (

    SELECT

        Salary,

        ROW_NUMBER() OVER (ORDER BY Salary) AS RowAsc,

        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowDesc

    FROM Employees

),

MedianCalculation AS (

    SELECT

        Salary

    FROM OrderedSalaries

    WHERE RowAsc = RowDesc

    UNION ALL

    SELECT

        AVG(Salary) AS Salary

    FROM OrderedSalaries

    WHERE RowAsc + 1 = RowDesc

)

SELECT

    AVG(Salary) AS MedianSalary

FROM MedianCalculation;

Explanation:

1. CTE OrderedSalaries:

   • ROW_NUMBER() OVER (ORDER BY Salary) AS RowAsc: Assigns a unique sequential integer to each salary ordered in ascending order.
   • ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowDesc: Assigns a unique sequential integer to each salary ordered in descending order.

2. CTE MedianCalculation:

   • WHERE RowAsc = RowDesc: Selects the salary when there is an odd number of rows, so there is exactly one middle value.
   • UNION ALL: Combines the results.
   • WHERE RowAsc + 1 = RowDesc: For an even number of rows, selects the average of the two middle values.

3. Final Query:

   • AVG(Salary) AS MedianSalary: Calculates the average of the median values, which handles both odd and even cases by averaging the middle values when necessary.

Example Data and Results

Assume the Employees table has the following salaries:

Salary
30000
40000
50000
60000
70000

For this dataset:

• The number of salaries is odd (5), so the median salary is the middle value: 50000.

If the dataset were:

Salary
30000
40000
50000
60000

• The number of salaries is even (4), so the median is the average of the two middle values: (40000 + 50000) / 2 = 45000.

Result

The query will return:

MedianSalary
50000

This result shows the median salary of the employees. You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find the median salary.

https://mytecbooks.blogspot.com/2024/08/get-median-salary-of-employees-query.html

Get the Median Salary of Employees Query Using MS SQL Server programs interview questions and answers freshers experienced advanced level