Oracle: Getting Simulated Cumulative Sum (Running Total)

Using Analytical Functions
select
    ename,
    dname,
    sal,
    sum(sal) over (partition by dname) dept_wise_total,
    sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
    sum(sal) over () total,
    sum(sal) over (order by dname, ename) running_total
from
    scott.emp e,
    scott.dept d
where
    e.deptno=d.deptno
order by
    dname, ename


---------------------------------------------------------------------------------------------------
Simulated Version
select
    ename,
    dname,
    sal,
    sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
    (select sum(sal) from scott.emp b where b.deptno=e.deptno and b.ename <= e.ename) sim_dept_runtotal,
    sum(sal) over (order by dname, ename) running_total,
    (select sum(sal) from scott.emp b where b.ename <= e.ename) sim_running_total
from
    scott.emp e,
    scott.dept d
where
    e.deptno=d.deptno
order by
    dname, ename

Output

















Related Links:
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- Top N query - 3rd highest salary
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- LAG and LEAD - Analytic Function
- FIRST_VALUE and LAST_VALUE with Windowing Clause

No comments:

Post a Comment