Cumulative running total

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 31 Oct 2008 07:48:44 -0600
Message-Id: <200810311348.m9VDmfrs001833@mail90c0.megamailservers.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 - 08:48:44 CDT

Original text of this message