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.
