CODEWITHSUNDEEP
sqloracle-databasefor-loopplsql
MasterC
MasterC

Reputation: 193

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?

Upvotes: 1

Views: 1965

Answers (3)

EJ Egyed
EJ Egyed

Reputation: 6094

Using a combination of LEAD and SUM analytic functions, you can determine which rows have the next PAM_A as a B, then only SUM if the next row is a B or the current row is a B.

Query

WITH
    d (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'
           THEN
               SUM (CASE WHEN next_pam_a = 'B' OR pam_a = 'B' THEN value_1 ELSE 0 END)
                   OVER (ORDER BY week)
           ELSE
               NULL
       END    value_2
  FROM (SELECT pam_a, week, value_1, LEAD (pam_a) OVER (ORDER BY week) AS next_pam_a FROM d);

Result

   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

Upvotes: 1

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270693

If I understand, you want a cumulative sum but with conditionality:

select t.*,
       (case when pam_A = 'B' then sum(value_1) over (order by week) end) as value_2
from t;

Upvotes: 0

Related Questions