MySQL Query to Calculate Salary Difference between Employees

Calculate the Difference between Current and Next Salary

Write a MySQL query to calculate the difference between the current salary and the next salary for each employee using a window function.

Solution:

-- Begin the SELECT statement to define the columns to retrieve 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 current employee's salary and the next employee's salary:
    -- LEAD(Salary, 1) retrieves the Salary of the next row (next EmployeeID in order).
    -- Subtracting this from the current Salary gives the SalaryDifference.
    -- ORDER BY EmployeeID ensures that the calculation follows the order of EmployeeID.
    Salary - LEAD(Salary, 1) OVER (ORDER BY EmployeeID) 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 the current salary and the next salary for each employee.
• This demonstrates the use of the LEAD() window function.
• Key Components:
• LEAD(Salary, 1) OVER (ORDER BY EmployeeID): Retrieves the salary from the next row.
• Salary - LEAD(Salary, 1): Computes the difference between the current salary and the next salary.
• Why use Window Functions?:
• Window functions allow you to access data from other rows in the result set, making it easier to perform row-to-row comparisons.
• Real-World Application:
• For example, in a company, you might want to analyze salary changes between employees to identify patterns or discrepancies.

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the percentage increase in salary from the previous employee.
• Write a MySQL query to compare each employee’s salary with the highest salary in the company.
• Write a MySQL query to compute the cumulative salary expense for a company.
• Write a MySQL query to rank employees based on salary, breaking ties by their joining date.

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

Previous MySQL Exercise: Find the Lead and Lag Values for Sales.
Next MySQL Exercise: Calculate the Cumulative Sum of Sales by Quarter.