CODEWITHSUNDEEP
sqloracle-database
Hallvard Johnsen
Hallvard Johnsen

Reputation: 31

How to conditionally accumulate values in Oracle SQL?

I have one column, 'C1', from which I want to calculate another column, desired_output, by adding values cumulatively (1,2,3,..) when C1=1, but then revert to zero when C1=0, before again starting to count upwards (1,2,3,..) when C1=1. Code as follows: 

    CREATE TABLE table1 (nr, id, int,c1 int,desired_output int);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,1,1,1);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,2,1,2);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,3,0,0);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,4,1,1);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,5,0,0);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,6,1,1);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,7,1,2);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,8,1,3);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,9,1,4);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,10,0,0);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,11,1,1);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,12,1,2);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,13,1,3);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,14,0,0);
    INSERT INTO table1 (nr, id, c1, desired_output) VALUES (1,15,1,1);

I have tried the code snippet (and various other smilar snippets)

    SELECT A.*, SUM(CASE WHEN C1 = 1 THEN 1 END) OVER 
    (PARTITION BY NR ORDER BY ROWNUM) AS OUTPUT_2
    FROM TABLE1 A;

but this only does half the trick, as the value of OUTPUT_2 does not revert to zero when C1=0.

Any ideas how to get around this?

Upvotes: 0

Views: 203

Answers (2)

Slkrasnodar
Slkrasnodar

Reputation: 824

with data (rn, nr, c1, desired_output) as
(
  select rownum, t.* from (select * from table1 /* order by some col */ ) t   
  -- http://searchoracle.techtarget.com/answer/What-is-Oracle-s-default-sorting-behavior 
  -- https://community.oracle.com/thread/3714691   
)
,
cte (rn, nr, c1, desired_output, do_2) as
(
  select rn, nr, c1, desired_output, case when c1 =0 then 0 else 1 end 
  from data where rn = 1
  union all
  select data.rn, data.nr, data.c1, data.desired_output, case when data.c1 = 0 then 0 else cte.do_2 + 1 end 
  from data join cte on (cte.rn + 1 = data.rn)
)
select * from cte;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You need to define the groups first. This requires an ordering, so let me assume you have a column called id that specifies the ordering of the rows.

Then the group can be defined as the cumulative sum of the number of 0s in c1:

select t1.*,
       sum(case when c1 = 0 then 1 else 0 end) over (order by id) as grp
from table1 t1;

Then, basically what you want is row_number() with some conditional logic:

select t1.*,
       (case when c1 = 0 then 0
             else row_number() over (partition by grp order by id)
        end) as newcol
from (select t1.*,
             sum(case when c1 = 0 then 1 else 0 end) over (order by id) as grp
      from table1 t1
     ) t1;

Your question is unclear on what nr is for. You may also need to partition all the analytic functions by nr.

Upvotes: 1

Related Questions