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?
Advertisement
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.