CODEWITHSUNDEEP
mysqlsqlselect
Kapil Shukla
Kapil Shukla

Reputation: 207

SQL to get cumulative salaries of all employees till the current month

Lets say we have Three employees in Employee_Info table:

Emp_Name Month salary
emp1 2021-01 10000
emp1 2021-02 13000
emp1 2021-03 10000
emp2 2021-01 15000
emp2 2021-02 15000
emp2 2021-03 12000
emp3 2021-01 20000
emp3 2021-02 20000
emp3 2021-03 13000

I have to write a select sql query with an output like this:

Emp_Name Month salary
emp1 2021-01 10000
emp1 2021-02 13000
emp1 2021-03 10000
emp1 2021-03 33000
emp2 2021-01 15000
emp2 2021-02 15000
emp2 2021-03 12000
emp2 2021-03 42000
emp3 2021-01 20000
emp3 2021-02 20000
emp3 2021-03 13000
emp3 2021-03 53000

As you see the new row which should have the sum of all salaries for a particular employee.

Upvotes: 1

Views: 1006

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Probably the simplest method is just to use union all:

select emp_name, month, salary
from t
union all
select emp_name, max(month), sum(salary)
from t
group by emp_name
order by emp_name, month, salary;

Note that if you want the cumulative salary on each row, then you can use window functions:

select t.*,
       sum(salary) over (partition by emp_name order by month) as running_salary
from t;

Here is a db<>fiddle.

Upvotes: 1

Related Questions