CODEWITHSUNDEEP
sqloracle-database
bytebiscuit
bytebiscuit

Reputation: 3496

Add values cumulative across years and months

Say I have the following data:

select 1 id, 'A' name, '2007' year, '04' month,  5 sales  from dual union all
select 2 id, 'B' name, '2007' year, '05' month,  2 sales  from dual union all
select 3 id, 'B' name, '2008' year, '12' month,  3 sales  from dual union all
select 4 id, 'B' name, '2009' year, '12' month, 56 sales  from dual union all
select 5 id, 'C' name, '2009' year, '08' month, 89 sales  from dual union all
select 6 id, 'A' name, '2010' year, '12' month,  4 sales  from dual union all
select 7 id, 'C' name, '2011' year, '12' month, 24 sales  from dual union all
select 8 id, 'D' name, '2012' year, '12' month, 15 sales  from dual union all
select 9 id, 'D' name, '2013' year, '12' month, 12 sales  from dual union all
select 10 id,'D' name, '2014' year, '12' month, 16 sales  from dual union all
select 11 id,'A' name, '2015' year, '07' month, 18 sales  from dual union all
select 12 id,'B' name, '2015' year, '09' month, 29 sales  from dual union all
select 13 id,'B' name, '2016' year, '01' month, 10 sales  from dual union all
select 14 id,'A' name, '2016' year, '02' month,  8 sales  from dual union all
select 15 id,'D' name, '2016' year, '03' month, 12 sales  from dual union all
select 16 id,'E' name, '2016' year, '04' month, 34 sales  from dual

I want to cumulatively add up all the sales across all years and their respective periods (months). The output should look like the following:

name    year    month   sale   opening bal   closing bal
 A      2007     04      5        0              5
 A      2007     05      2        5              7
 B      2008     12      3        12             15
 B      2009     12      56       15             71
 C      2009     08      89       71             160
 A      2010     12      4        160            164
 C      2011     12      24       164            188
 D      2012     12      15       188            203
 D      2013     12      12       203            215
 D      2014     12      16       215            231
 A      2015     07      18       231            249
 B      2015     09      29       249            278
 B      2016     01      10       278            288
 A      2016     02      8        288            296
 D      2016     03      12       296            308
 E      2016     04      34       308            342

The Opening balance is the closing balance of previous month, and if it goes into next year than the opening balance for next year is the closing balance of the previous year. It should be able to work like this for subsequent years.

I'm working on Oracle 12c.

Thanks in advance.

Upvotes: 2

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You can do these calculations using window functions:

select t.*,
       (sum(s) over (order by year, month) - s) as opening
       sum(s) over (order by year, month) as closing
from t;

The opening balance could also use a range/rows windowing clause:

select t.*,
       sum(s) over (order by year, month rows between unbounded preceding and 1 preceding) as opening
       sum(s) over (order by year, month) as closing
from t;

However, I think the subtraction is a simpler way.

Upvotes: 2

Related Questions