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.

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

ReplyDeleteYour article really helped to solve an issue. Thanks.

ReplyDeleteThanks for a quick and complete example of something I needed to do.

ReplyDeleteI 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

Thank you very much for a helpful example.

ReplyDeleteThe 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.

ReplyDeleteIf 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.

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

Check out this link

ReplyDeleteCumulative sum in oracle

This comment has been removed by the author.

ReplyDeleteHi..I have a small class project in which i have to generate a report like this

ReplyDeletea 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?