# Re: Cumulative running total

From: <jo_holvoet_at_amis.com>
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

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-l
```
Received on Fri Oct 31 2008 - 09:56:43 CDT

Original text of this message