Running total or Cumulative Sum using analytic functions

In this article we will how we will be able to achieve the cumulative sum or running total of a column using oracle supplied analytical functions:

 

Consider the following example:

 

CREATE TABLE dept (deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20));

 

CREATE TABLE emp (eno NUMBER PRIMARY KEY, ename VARCHAR2(35), deptno NUMBER REFERENCES dept (deptno), sal NUMBER);

 

INSERT INTO dept VALUES (10, ‘Accounts’);

 

INSERT INTO dept VALUES (20, ‘HR’);

 

INSERT INTO emp VALUES (1, ‘A’, 10, 1000);

 

INSERT INTO emp VALUES (2, ‘B’, 10, 1500);

 

INSERT INTO emp VALUES (3, ‘C’, 20, 2000);

 

INSERT INTO emp VALUES (4, ‘D’, 20, 3500);

 

INSERT INTO emp VALUES (5, ‘E’, 20, 1500);

 

 

Now what we are going to achieve is the following result:

 

ENO   ENAME       DEPTNO   SAL       CUM_SAL

1     A           10       1000      1000

2     B           10       1500      2500

3     C           20       2000      2000

4     D           20       3500      5500

5     E           20       1500      7000

 

As per the above result what we require is clear. That is we are going to get department-wise running total of salary of all employees.

 

Now consider the following query:

 

select eno, ename, deptno, sal, sum(sal) over (partition by deptno order by deptno, eno, ename) cum_sal

from emp

 

And its output:

 

ENO   ENAME   DEPTNO   SAL   CUM_SAL

1      A      10       1000   1000

2      B      10       1500   2500

3      C      20       2000   2000

4      D      20       3500   5500

5      E      20       1500   7000

 

This is same as we desired. But how is this result achieved?

 

The crux for this is the analytic function which is used in the query.

 

The syntax for using an analytical function is as follows:

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )

 

We will not delve deeper into each and every function available. In this example, we have used SUM () and OVER (PARTITION BY … ORDER BY …).

 

The PARTITION BY clause in OVER () determines where the data needs to be partitioned (not grouped). The ORDER BY clause orders the partitioned data. The SUM () in our case takes the sum of the values returned by the OVER ().

 

This concludes the example for running total using analytical functions. Look for more articles on analytic functions soon.

9 comments :

  1. Good Article. It was easy to implement the logic from this article. Thanks.

    ReplyDelete
  2. Your article really helped to solve an issue. Thanks.

    ReplyDelete
  3. Thanks for a quick and complete example of something I needed to do.

    I love Oracle analytic functions.

    Also for people who want to do more analytic functions I recommend this book. Advanced SQL Functions in Oracle 10g (Paperback) by Richard Earp.

    Eric Tremblay
    eric.tremblay@data-warehouse.ca

    ReplyDelete
  4. Thank you very much for a helpful example.

    ReplyDelete
  5. The same example is repeated on all website. You did same. What if any employee does not receive salary or salary is 0. Try to give example for that, rather than just copy paste from other websites.

    ReplyDelete
  6. If the employee does not receive any salary or if salary is 0, even then the result will not be different. The example teaches the usage of analytic function, and its just a beginners level.

    It is just the data that is different which you pointed out.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi..I have a small class project in which i have to generate a report like this

    a b c row_total cumulative

    12 10 12 34 34
    13 10 12 35 69
    10 10 10 30 99

    total column:a:35
    b:30
    c:34

    Can anyone please suggest me the oracle query for this report?

    ReplyDelete