sqlselectoracle9i

Cumulative calculation in Oracle

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
Solution

  • 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