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.