Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions

SELECT
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,ENAME) CUMDEPTTOT,
    SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTAL,
    SUM(SAL) OVER (ORDER BY DEPTNO, SAL) CUMTOT,
    SUM(SAL) OVER () TOTSAL
FROM
    SCOTT.EMP
ORDER BY
    DEPTNO,
    SAL




Related Links:
- SQL Puzzle - Issue Tracker - with Solution Approach
- Getting Cumulative Sum (Running Total) without Analytic Function
- Rank, Dense_Rank, Row_Number Analytic Functions
- LAG and LEAD - Analytic Function
- FIRST_VALUE and LAST_VALUE with Windowing Clause
- Partitioned Outer Join - Fill Gaps in Sparse Data

12 comments:

  1. thanx 4 nice tutorial..

    ReplyDelete
  2. good one. searching this for long time

    ReplyDelete
    Replies
    1. Also Check related posts of this article.

      Delete
  3. Nice example. Get to know new things about analytical functions
    Thanks.

    ReplyDelete
  4. Nice example. Get to know new things about analytical functions
    Thanks.

    ReplyDelete
  5. Thanks a lot Nimish. It helps a lot.

    ReplyDelete
  6. CUMTOT need to display below values
    1300
    3750
    8750
    9550
    10650
    13625
    16625
    19625
    20575
    21825
    23075
    24575
    26175
    29025

    ReplyDelete
    Replies
    1. I believe CUMTOT is displaying correctly based on the ORDER BY of (DEPTNO,SAL). To generate the answer you are seeking, the ORDER BY needs to change to (DEPTNO,ENAME).

      Delete
  7. I lkke this web ssite very much, Its a real nice place to read
    and receive information.

    ReplyDelete
  8. Thanks Nimish, just what I've been looking for. Saved me heaps of time :-)

    ReplyDelete