MySQL Query to Calculate Salary Difference from Department Average

Calculate the Salary Difference from Department Average

Write a MySQL query to calculate the difference between each employee's salary and the average salary of their department using a window function.

Solution:

-- Begin the SELECT statement to retrieve specified columns from the Employees table
SELECT 
    -- Retrieve the DepartmentID to identify the department each employee belongs to
    DepartmentID, 
    -- Retrieve the EmployeeID as the unique identifier for each employee
    EmployeeID, 
    -- Retrieve the Name to display the employee's name
    Name, 
    -- Retrieve the Salary to show each employee's current salary
    Salary,
    -- Calculate the difference between the employee's Salary and the average salary of their department:
    -- AVG(Salary) OVER (PARTITION BY DepartmentID) computes the average salary for each department,
    -- subtracting this average from the employee's salary gives the SalaryDifference
    Salary - AVG(Salary) OVER (PARTITION BY DepartmentID) AS SalaryDifference
-- Specify the source table containing employee records
FROM Employees;

Explanation:

• Purpose of the Query:
• The goal is to calculate the difference between each employee's salary and the average salary of their department.
• This demonstrates the use of the AVG() window function with the PARTITION BY clause.
• Key Components:
• AVG(Salary) OVER (PARTITION BY DepartmentID): Calculates the average salary for each department.
• Salary - AVG(Salary) OVER (PARTITION BY DepartmentID): Computes the difference between the employee's salary and the department average.
• Why use Window Functions?:
• Window functions allow you to perform calculations within specific partitions of your data, making it easier to compare individual values to group averages.
• Real-World Application:
• For example, in a company, you might want to analyze how individual salaries compare to the department average to identify outliers or inequities.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find employees earning below the department’s average salary.
• Write a MySQL query to calculate the difference between each employee's salary and the company-wide average salary.
• Write a MySQL query to list employees whose salary is within 10% of their department’s average.
• Write a MySQL query to rank employees based on how much above or below the department average they earn.

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

Previous MySQL Exercise: Find the Top 3 Highest Paid Employees in Each Department.
Next MySQL Exercise: Find the First and Last Sale Date for Each Customer.