Calculating cumulative figures by month 1

[robo100]

I have the following in a table

Store Period Year Date Value
1 1 2004 1/1/04 100
1 2 2004 1/2/04 200
1 3 2004 1/3/04 100
2 1 2004 1/1/04 50
2 2 2004 1/2/04 75

I want to write a view to show the cumulative value by month so the data would look as follows.

Store Period Year Date Cum_Value
1 1 2004 1/1/04 100
1 2 2004 1/2/04 300
1 3 2004 1/3/04 400
2 1 2004 1/1/04 50
2 2 2004 1/2/04 125

Any help would be appreciated as I am struggling with this.

Thanks in advance.

 

[Dagon]

One possibility is use an inline select statement:

select store, period, year, date, amount, 
(select sum(amount) from amounts a2
where a1.store = a2.store
and a2.date <= a1.date) cum_amount
from amounts a1

 

[SantaMufasa]

Just to perhaps "gild the lily" that Dagon so well presented, you will probably want to add:

...
order by store, period

...since you cannot rely upon the "physical" order of rows in an Oracle table.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:31 (23Sep04) UTC (aka "GMT" and "Zulu"), 10:31 (23Sep04) Mountain Time)