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

17 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
  9. Very descriptive blog, I enjoyed thaat a lot. Will
    tere be a part 2?

    ReplyDelete
  10. I tһink the admin of thiѕ web ρage is reaⅼly
    workіng haгd fоr hiѕ website, since here everу data is quality based data.

    ReplyDelete
  11. Good site you have here.. It's hard to find excellent writing like yours nowadays.
    I truly appreciate people like you! Take care!!

    ReplyDelete
  12. Thank you for sharing your thoughts. I really appreciate your efforts and I
    am waiting for your next write ups thank yoou oncee again.

    ReplyDelete