How to find the cumulative salary with each year?

Question

The output should be the last column (Cum_Sal).

 EMPLOYEE   MONTH   YEAR    SALARY Cum_Sal
 A            JAN   2016    2000     2000
 A            FEB   2016    2000     4000
 A            MAR   2016    2000     6000
 A            APR   2016    3000     9000
 A            MAY   2016    3000     12000
 A            JUN   2016    3000     15000
 A            JUL   2016    3000     18000
 A            AUG   2016    3000      .
 A            SEP   2016    3000       .
 A            OCT   2016    3000
 A            NOV   2016    3000
 A            DEC   2016    3000
 A            JAN   2017    3000     3000
 A            FEB   2017    3000     6000
 A            MAR   2017    3000     9000
 A            APR   2017    5000      .
 A            MAY   2017    5000       . (SO ON)
 A            JUN   2017    5000
 A            JUL   2017    5000
 A            AUG   2017    5000

Thanks for your help. SM

Don't do this, use programming language to calculate cumulative salary after per month. — Vy Do, Jan 13 '18 at 04:33
@DoNhuVy - Why not in sql ? This is pretty much straight forward in Sql — Pரதீப், Jan 13 '18 at 04:40
Possible duplicate of [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — AmourK, Jan 13 '18 at 04:40
@ Pரதீப் , when update salary per month, cumulative salary quite hard to update accordingly. then for updating cumulative salary, you must use trigger, it is quite cumbersome and data integrity is not assured. — Vy Do, Jan 13 '18 at 04:44
@DoNhuVy - Did OP mentioned anywhere that the cumulative sum needs to be stored ? — Pரதீப், Jan 13 '18 at 04:57
It would be nice if you tagged the `dbms` you're using, and also can you post what have you tried so far? SO isn't a code delivery system. — Paul Karam, Jan 13 '18 at 08:29

score 0 · Answer 1 · answered Jan 13 '18 at 08:02

0

I am not sure the below query is perfect or not , but this solution helps you to find out the correct solution !

select
    EMPLOYEE, MONTH, YEAR ,SALARY ,sum(SALARY) over(order by EMPLOYEE) as Cum_Sal
from emp

answered Jan 13 '18 at 08:02

Usman

1,983
15
28

score 0 · Answer 2 · answered Dec 01 '19 at 09:45

0

This is the answer you were looking for.

select EMPLOYEE, MONTH, YEAR ,SALARY ,sum(SALARY) over (partition by YEAR order by SALARY rows between unbounded preceding and current row  ) as Cum_Sal  from emp

answered Dec 01 '19 at 09:45

DJKarma

172
9

How to find the cumulative salary with each year?

2 Answers2