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

24 comments:

  1. thanx 4 nice tutorial..

    ReplyDelete
  2. good one. searching this for long time

    ReplyDelete
  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
  13. Nice enanhancment . I think it's from 18c version. In your. Report first column is dept no for all order by clauase. That's why report is right

    ReplyDelete
    Replies
    1. ohh okay I but never heard , read , used , or got requirement when was working on that till 12c . Might be i missed that time

      Delete
    2. Some of these are as old as oracle 8i/9i

      Delete
    3. but in this query order by clause not needed windowing clause . Order by itself performing running total. There is no rows and range keywords . That's why I asked

      Delete
    4. I have used order by with analytic function where I am calculating cumulative sum (running total), simple total does not need order by here, as in DEPTTOTAL and TOTALSAL columns

      Delete
  14. I think order by deptno,sal at the end is not required.

    ReplyDelete