René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Using Oracle's analytical function to calculate a cumulative sum

This example shows how to use the analytical function SUM to perform a cumulative sum. First, we fill some values in a table. The table is very simple and consists of the field dt and xy only. Note, that for a given date it is possible to insert multiple rows which is exactly what I do here.
What I am interested is to extract the cumulative sum for each day in the table. That is, if I have three entries for the same date, for example 3, 4 and 5, I don't want the sum to only be 3+4+5 for each row, but 3 for the first row, 3+4 for the second row and 3+4+5 for the third row. 
create table sum_example (
  dt date,
  xy number
);

insert into sum_example values (to_date('27.08.1970','DD.MM.YYYY'),4);
insert into sum_example values (to_date('02.09.1970','DD.MM.YYYY'),1);
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),5);
insert into sum_example values (to_date('26.08.1970','DD.MM.YYYY'),3);
insert into sum_example values (to_date('28.08.1970','DD.MM.YYYY'),4);
insert into sum_example values (to_date('26.08.1970','DD.MM.YYYY'),6);
insert into sum_example values (to_date('29.08.1970','DD.MM.YYYY'),9);
insert into sum_example values (to_date('30.08.1970','DD.MM.YYYY'),2);
insert into sum_example values (to_date('12.09.1970','DD.MM.YYYY'),7);
insert into sum_example values (to_date('23.08.1970','DD.MM.YYYY'),2);
insert into sum_example values (to_date('27.08.1970','DD.MM.YYYY'),5);
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),9);
insert into sum_example values (to_date('01.09.1970','DD.MM.YYYY'),3);
insert into sum_example values (to_date('07.09.1970','DD.MM.YYYY'),1);
insert into sum_example values (to_date('12.09.1970','DD.MM.YYYY'),4);
insert into sum_example values (to_date('03.09.1970','DD.MM.YYYY'),5);
insert into sum_example values (to_date('03.09.1970','DD.MM.YYYY'),8);
insert into sum_example values (to_date('07.09.1970','DD.MM.YYYY'),7);
insert into sum_example values (to_date('04.09.1970','DD.MM.YYYY'),8);
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),1);
insert into sum_example values (to_date('29.08.1970','DD.MM.YYYY'),3);
insert into sum_example values (to_date('30.08.1970','DD.MM.YYYY'),7);
insert into sum_example values (to_date('24.08.1970','DD.MM.YYYY'),7);
insert into sum_example values (to_date('07.09.1970','DD.MM.YYYY'),9);
insert into sum_example values (to_date('26.08.1970','DD.MM.YYYY'),2);
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),8);

select dt, sum(xy) over (partition by trunc(dt) order by dt rows between 
unbounded preceding and current row) s, xy from sum_example;

drop table sum_example;
The select statement will return: 
23.08.70          2          2
24.08.70          7          7
26.08.70          3          3
26.08.70          5          2
26.08.70         11          6
27.08.70          4          4
27.08.70          9          5
28.08.70          4          4
29.08.70          9          9
29.08.70         12          3
30.08.70          2          2
30.08.70          9          7
01.09.70          3          3
02.09.70          1          1
03.09.70          5          5
03.09.70         13          8
04.09.70          8          8
07.09.70          1          1
07.09.70          8          7
07.09.70         17          9
09.09.70          5          5
09.09.70         14          9
09.09.70         15          1
09.09.70         23          8
12.09.70          7          7
12.09.70         11          4
The third column correspondents to xy (the values inserted with the insert into ... above). The interesting column is the second. For example on the 26th of August in 1970, the first row for that date is 3 (equals xy), the second is 5 (equals xy+3) and the third is 11 (equals xy+3+5).