# Re: Cumulative running total

Date: Fri, 31 Oct 2008 15:56:43 +0100

Message-ID: <OFCE369C0B.76905A11-ONC12574F3.0051DB48-C12574F3.00521999@amis.com>

The following works but is pretty ugly :

select A

*, B*

*, year*

*, period*

*, sum(amount)*

over (partition by A, B order by A, B, PERIOD) AMOUNT_SUM

from (

select tt.a, vv.b, tt.year, tt.period, case when vv.b = tt.b then tt.amount else 0 end amount

from test tt,

(select distinct t1.year, t1.period, t2.b from test t1, test t2) vv

where vv.year = tt.year (+)

and vv.period = tt.period (+)

)

**WHERE YEAR = 2008**

and A = 'BV'

AND PERIOD <= 7

**ORDER BY PERIOD, A, B;**The cartesian join is to get all the rows you want but if the table is large ...

I'm sure someone will come up with something much cleaner using just analytics.

mvg/regards

Jo

Wolfgang Breitling <breitliw_at_centrex To cc.com> Oracle L <oracle-l_at_freelists.org> Sent by: cc oracle-l-bounce_at_f reelists.org Subject Cumulative running total 31/10/2008 14:51 Please respond to breitliw_at_centrexc c.com

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

-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 31 2008 - 09:56:43 CDT