# Cumulative running total

*From*: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>*To*: Oracle L <oracle-l@xxxxxxxxxxxxx>*Date*: Fri, 31 Oct 2008 07:48:44 -0600

I have the following table: Name Null? Type ----------------- -------- ------------ A VARCHAR2(6) B VARCHAR2(6) YEAR NOT NULL NUMBER(38) PERIOD NOT NULL NUMBER(38) AMOUNT NOT NULL NUMBER(26,3) and the following content: SQL> select * from test where year = 2008 and a = 'BV'; A B YEAR PERIOD AMOUNT ------ ------ ---------- ---------- ---------- BV BS 2008 1 100 BV AS 2008 2 200 BV BS 2008 3 300 BV AS 2008 4 400 BV BS 2008 5 500 BV AS 2008 6 600 BV BS 2008 7 700 BV AS 2008 8 800 BV BS 2008 9 900 BV AS 2008 10 1000 BV BS 2008 11 1100 BV AS 2008 12 1200 BV BS 2008 13 1300 BV AS 2008 14 1400

`Note that there are gaps for each B. Here BSs are only present for`

`odd periods, ASs only for even periods ( that was an easy way to simulate it ).`

What I need is a cumulative running total by period: A B YEAR PERIOD AMOUNT_SUM ------ ------ ---------- ---------- ---------- BV BS 2008 1 100 BV AS 2008 2 200 BV BS 2008 2 100 BV AS 2008 3 200 BV BS 2008 3 400 BV AS 2008 4 600 BV BS 2008 4 400 BV AS 2008 5 600 BV BS 2008 5 900 BV AS 2008 6 1200 BV BS 2008 6 900 BV AS 2008 7 1200 BV BS 2008 7 1600 ... But when I use the sum() analytic function select A , B , year , period , sum(amount) over (partition by A, B order by A, B, PERIOD) AMOUNT_SUM FROM test WHERE YEAR = 2008 and A = 'BV' AND PERIOD <= 7 ORDER BY PERIOD, A, B; I only get A B YEAR PERIOD AMOUNT_SUM ------ ------ ---------- ---------- ---------- BV BS 2008 1 100 BV AS 2008 2 200 BV BS 2008 3 400 BV AS 2008 4 600 BV BS 2008 5 900 BV AS 2008 6 1200 BV BS 2008 7 1600

`i.e. for periods where B does not have a value in the base table`

`there is no cumulative sum. I need the prior period's cumulative sum`

`carried forward.`

`Anybody have a bright idea how to accomplish that in a single SQL.`

`The database is 9.2.0.6.`

`Maybe wrapping the sql in another analytic function with a window`

`looking back to the prior row.`

Regards Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com

**Follow-Ups**:**Re: Cumulative running total***From:*Gints Plivna

**Re: Cumulative running total***From:*jo_holvoet

**References**:**RAC alert log viewer for Windows***From:*Dan Norris