Cumulative calculation in Oracle

0
votes

I have below Emp table that contains three columns (empno, ename, salary).

empno  ename   salary 
---------------------
 101   Ram     2000
 102   Kumar   1000
 103   Mani    3000

I need below output by displaying Cumulative_Salary from above table.

Empno Ename  Cumulative_Salary
-------------------------------
101   Ram    2000
102   Kumar  3000
103   Mani   6000
2
sqlselectoracle9i

2 Answers

0
votes

You are looking for SUM OVER.

select 
  empno, ename, salary,
  sum(salary) over(order by empno) as cumulative_salary
from emp
order by empno;

By applying ORDER BY empno in the OVER clause, we implicitly apply RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, but you can of course make this explicit, if you want to.

Docs: https://oracle-base.com/articles/misc/sum-analytic-function

0
votes
    select 
      empno
     , ename
     , sum(salary) as cumulative_salary
    from emp
    group by
      empno, ename
    order by empno;