user9006529 user9006529 - 9 months ago 122
MySQL Question

Find salary difference between employee

Need to find salary difference between employees.

I have a table employee.

table

My result should be

0

1000

5000

26000

How to write query?

Thanks in advance for any help.

Answer Source

One option uses a correlated subquery:

SELECT
    emp_id,
    emp_name,
    emp_salary - COALESCE((SELECT emp_salary FROM employee e2
              WHERE e2.emp_id = e1.emp_id - 1), emp_salary) AS salary
FROM
    employee e1
ORDER BY emp_id;

Demo

Another option would be to use a join:

SELECT
    e1.emp_id,
    e1.emp_name,
    e1.emp_salary - COALESCE(e2.emp_salary, e1.emp_salary) AS salary
FROM employee e1
LEFT JOIN employee e2
    ON e1.emp_id = e2.emp_id + 1
ORDER BY
    emp_id;

Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download