CODEWITHSUNDEEP
oracle-database
mssstack1
mssstack1

Reputation: 11

Reset Running total when either column value changes using Oracle Analytics

I wants to add a column to the view which keeps a running total of the Value column. This column must reset the running total when either Cat A or Cat B changes in the rows. So the output data must be like this:-

CAT A  CAT B    Value   Running Total
=====  =====    =====   =============
0        1       15 
0        1       235 
0        1       306      556
0        0        
1        2       85        85
1        1       105      105 
0        2       600
0        2       70       670
0        3       564
0        3       101      665
0        1       30        30

Upvotes: 0

Views: 1148

Answers (2)

Al Kannan
Al Kannan

Reputation: 21

Reset in a partition can be done by introducing a new column that will enable the partition split. In your case if you create a new column that have a unique value based either CAT A or CAT B change, this will allow you run analytic function at subset level, you partition will include your already using one + this new column...this essentially does the reset you are looking for

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

First, you need a column to specify the ordering for the running sum. I will assume this is called id.

Second, you seem to have some logic that does not have values on certain rows. It is not clear what this logic is. A proper "running sum" would be defined on all rows.

Then, you can use lag() and sum() to define the groups and use "analytic" sum() for the running sum:

select t.cata, t.catb, t.value,
       sum(value) over (partition by grp order by id) as runningsum
from (select t.*,
             sum(case when cata = prev_cata and catb = prev_catb then 0
                      else 1
                  end) over (order by id) as grp
      from (select t.*, lag(cata) over (order by id) as prev_cata,
                   lag(catb) over (order by id) as prev_catb
            from t
           ) t
     ) t;

Upvotes: 1

Related Questions