How to calculate Salary Difference from Company Average in MySQL

Calculate the Salary Difference from Company Average

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

Solution:

-- Begin the SELECT statement to define the columns to be retrieved from the Employees table
SELECT 
    -- Retrieve the EmployeeID column to uniquely identify each employee
    EmployeeID, 
    -- Retrieve the Name column to display the employee's name
    Name, 
    -- Retrieve the Salary column to show the employee's current salary
    Salary,
    -- Calculate the difference between the employee's Salary and the average salary of all employees:
    -- AVG(Salary) OVER () computes the overall average salary across all employees,
    -- Subtracting this average from the employee's salary gives the SalaryDifference
    Salary - AVG(Salary) OVER () AS SalaryDifference
-- Specify the Employees table as the source of the data
FROM Employees;

Explanation:

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

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the difference between each employee's salary and the department average using a window function.
• Write a MySQL query to find the median salary of all employees using a window function.
• Write a MySQL query to rank employees based on their salary within their respective departments.
• Write a MySQL query to find employees who earn above the average salary of their department.

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

Previous MySQL Exercise: Find the Median Salary in Each Department.
Next MySQL Exercise: Find the Cumulative Distribution of Sales.