Oracle PL/SQL SUM OVER( ) starting from certain row

I have a table who looks like this:

Pam_A   Week   Value_1  
A       1        10
A       2        13
B       3        15
B       4        10
B       5        11 
B       6        10

I want to achieve the following:

Pam_A   Week   Value_1  Value_2
A       1        10
A       2        13
B       3        15      28  
B       4        10      38
B       5        11      49
B       6        10      59

When Pam_A=B, sum the current Value_1 and its preceding row value and keep that value increasing accordding the next value in Value_1

Any ideas for achieve this cumulative sum?

Answer

First of all you need to mark all rows that you want to count. You can do it like this:

with t(Pam_A, Week, Value_1) as (
   select 'A',       1,        10 from dual union all
   select 'A',       2,        13 from dual union all
   select 'B',       3,        15 from dual union all
   select 'B',       4,        10 from dual union all
   select 'B',       5,        11 from dual union all 
   select 'B',       6,        10 from dual
)
   select
      Pam_A, Week, Value_1
     ,case 
         when Pam_A='B' or lead(Pam_A)over(order by week) = 'B' 
          then 'Y' 
          else 'N' 
      end as flag
   from t;

Results:

PAM_A       WEEK    VALUE_1 FLAG
----- ---------- ---------- ----
A              1         10 N
A              2         13 Y
B              3         15 Y
B              4         10 Y
B              5         11 Y
B              6         10 Y

6 rows selected.

Then you can aggregate only rows that have flag=’Y’:

with t(Pam_A, Week, Value_1) as (
   select 'A',       1,        10 from dual union all
   select 'A',       2,        13 from dual union all
   select 'B',       3,        15 from dual union all
   select 'B',       4,        10 from dual union all
   select 'B',       5,        11 from dual union all 
   select 'B',       6,        10 from dual
)
select
   v.*
  ,case 
      when flag='Y' and Pam_a='B'
         then sum(Value_1)over(partition by flag order by Week) 
   end as sums
from (
   select
      Pam_A, Week, Value_1
     ,case 
         when Pam_A='B' or lead(Pam_A)over(order by week) = 'B' 
          then 'Y' 
          else 'N' 
      end as flag
   from t
) v;

Results:

PAM_A       WEEK    VALUE_1 FLAG       SUMS
----- ---------- ---------- ---- ----------
A              1         10 N
A              2         13 Y
B              3         15 Y            28
B              4         10 Y            38
B              5         11 Y            49
B              6         10 Y            59

6 rows selected.