What query should I write to calculate the cumulative salary of employees from the salary table

user2803850 picture user2803850 · Sep 26, 2013 · Viewed 13.4k times · Source

A salary table is given and we need to write a single query which prints the cumulative salary of employees. For Example:

Emp  Sal
A    10
B    15
C    20
D    5


OutPut:

Emp  Sal   Cum_Sal
A    10      10
B    15      25
C    20      45
D    5       50

Answer

Roman Pekar picture Roman Pekar · Sep 26, 2013

in some RDBMS (like PostgreSQL or SQL Server 2012 or Oracle) it's possible to use window function:

select
    Emp, Sal, sum(Sal) over(order by Emp) as Cum_Sal
from employees

if it's not possible, you can use self-join

select
    e1.Emp, e1.Sal, sum(e2.Sal) as Cum_Sal
from employees e1
    inner join employees e2 on e2.Emp <= e1.Emp
group by e1.Emp, e1.Sal

sql fiddle demo

Note that in both cases you have to define order for rolling sum (in queries above, it's ordered by Emp column).