# Re: Cumulative running total

Date: Fri, 31 Oct 2008 10:23:06 -0700

Message-ID: <26fdee6e0810311023r4df587aai8b44fe6277b7dc9f@mail.gmail.com>

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_at_gmail.com>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_at_gmail.com>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_at_centrexcc.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*

*>>*

*>>*

*>>*

*>*

-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 31 2008 - 12:23:06 CDT