Re: Cumulative running total

• From: "Michael Moore" <michaeljmoore@xxxxxxxxx>
• To: gints.plivna@xxxxxxxxx
• Date: Fri, 31 Oct 2008 10:23:06 -0700
```let me try again...
not sure if this matters, but there is no AS for period 1 in the result set
for Gint's first solution.
Mike

On Fri, Oct 31, 2008 at 10:20 AM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:

> not sure if this matters, but there is no AS for period 1 in the result set
> for this solution.
>
>
> On Fri, Oct 31, 2008 at 7:55 AM, Gints Plivna <gints.plivna@xxxxxxxxx>wrote:
>
>> Not sure whether it is the most performant and elegant way, but it works
>> :)
>>
>> The main idea is somehow generate necessary a, b, year and period
>> combinations (inlene view t1), then left join it to your already done
>> calculations (inline view t2) and then pick max value for each row
>> (remember that default for window for windowing functions is from the
>> first row to current row, that's why max produces correct amount).
>>
>> SELECT t1.a, t1.alt_b, t1.year, t1.period,
>>  max(amount_sum) OVER (partition by alt_b ORDER BY t1.period) arr_amount
>> FROM (
>>  (SELECT * FROM (
>>     SELECT a, year, period, lag(b) OVER (ORDER BY period, a, b) alt_b FROM
>> (
>>       SELECT a, b, year, period FROM test
>>       UNION ALL
>>      SELECT a, b, year, period FROM test
>>     )
>>     ORDER BY period, a, alt_b
>>  )
>>  WHERE alt_b IS NOT NULL
>>  ) t1
>>  LEFT JOIN (
>>     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') t2
>>  ON (t1.alt_b = t2.b AND t1.year = t2.year AND t1.period = t2.period))
>> ORDER BY PERIOD, A, alt_b;
>> 1       BV      BS      2008    1       100
>> 2       BV      AS      2008    2       200
>> 3       BV      BS      2008    2       100
>> 4       BV      AS      2008    3       200
>> 5       BV      BS      2008    3       400
>> 6       BV      AS      2008    4       600
>> 7       BV      BS      2008    4       400
>> 8       BV      AS      2008    5       600
>> 9       BV      BS      2008    5       900
>> 10      BV      AS      2008    6       1200
>> 11      BV      BS      2008    6       900
>> 12      BV      AS      2008    7       1200
>> 13      BV      BS      2008    7       1600
>> 14      BV      AS      2008    8       2000
>> 15      BV      BS      2008    8       1600
>> 16      BV      AS      2008    9       2000
>> 17      BV      BS      2008    9       2500
>> 18      BV      AS      2008    10      3000
>> 19      BV      BS      2008    10      2500
>> 20      BV      AS      2008    11      3000
>> 21      BV      BS      2008    11      3600
>> 22      BV      AS      2008    12      4200
>> 23      BV      BS      2008    12      3600
>> 24      BV      AS      2008    13      4200
>> 25      BV      BS      2008    13      4900
>> 26      BV      AS      2008    14      5600
>> 27      BV      BS      2008    14      4900
>>
>> Gints Plivna
>> http://www.gplivna.eu
>>
>> 2008/10/31 Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>:
>> > 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
>>
>>
>>
>
```