MySQL Query to Find the Median Salary in Each Departmente

Find the Median Salary in Each Department

The median salary in each department is the middle value of all salaries in that department when sorted in ascending order. If there is an odd number of employees, it is the exact middle value; if even, it is the average of the two middle values. It provides a measure of central tendency that is less affected by extreme outliers compared to the mean.

Write a MySQL query to find the median salary in each department using a window function.

Solution:

-- Define a Common Table Expression (CTE) named RankedSalaries to rank salaries within each department
WITH RankedSalaries AS (
    -- Begin the inner SELECT statement to compute ranking and total count of salaries per department
    SELECT 
        -- Retrieve the DepartmentID to group employees by department
        DepartmentID, 
        -- Retrieve the Salary column to work with employee salaries
        Salary,
        -- Assign a row number to each salary within its department, ordered in ascending order
        ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary) AS RowNum,
        -- Compute the total number of employees in each department
        COUNT(*) OVER (PARTITION BY DepartmentID) AS TotalCount
    -- Specify the Employees table as the source
    FROM Employees
)
-- Begin the main SELECT statement to compute the median salary per department
SELECT 
    -- Retrieve the DepartmentID to display results by department
    DepartmentID, 
    -- Compute the average salary for the selected median rows to handle both even and odd counts
    AVG(Salary) AS MedianSalary
-- Specify the source as the previously defined CTE 'RankedSalaries'
FROM RankedSalaries
-- Select only the row(s) corresponding to the median salary:
-- If the total count is odd, (TotalCount + 1) / 2 selects the middle row.
-- If the total count is even, both (TotalCount + 1) / 2 and (TotalCount + 2) / 2 select the two middle rows.
WHERE RowNum IN ((TotalCount + 1) / 2, (TotalCount + 2) / 2)
-- Group by DepartmentID to compute the median salary for each department
GROUP BY DepartmentID;

Explanation:

• Purpose of the Query:
• The goal is to calculate the median salary for each department.
• This demonstrates the use of the ROW_NUMBER() and COUNT() window functions to compute the median.
• Key Components:
• ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary): Assigns a row number to each salary within the department.
• COUNT(*) OVER (PARTITION BY DepartmentID): Calculates the total number of employees in each department.
• WHERE RowNum IN ((TotalCount + 1) / 2, (TotalCount + 2) / 2): Filters the rows to find the median salary.
• Why use Window Functions?:
• Window functions allow you to perform complex calculations, such as finding the median, without the need for multiple subqueries.
• Real-World Application:
• For example, in a company, you might want to analyze the median salary in each department to understand the typical compensation level..

For more Practice: Solve these Related Problems:

• Write a MySQL query to find the median salary across the entire company.
• Write a MySQL query to find the department with the highest median salary.
• Write a MySQL query to compute the median salary while excluding the highest and lowest 5% of salaries.
• Write a MySQL query to determine the difference between the median and average salary in each department.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Calculate the Percentage of Total Sales for each Product.
Next MySQL Exercise: Calculate the Difference between Each Employee's Salary and the Company Average.