I have a sequential dataset where I need to sum values by distinct groups, which are already parsed. Every time a 1 appears in the Distinct Group
column, I want to start the sum again.
Thanks so much in advance!! :)
Original Table:
Customer | Order_col | Name | Val | Distinct_Group
1 | 1 | A | 1 | 1
1 | 2 | A | 2 | 0
1 | 3 | A | 3 | 0
1 | 4 | B | 4 | 1
1 | 5 | C | 5 | 1
1 | 6 | C | 6 | 0
2 | 1 | A | 1 | 1
2 | 2 | B | 2 | 1
2 | 3 | D | 3 | 1
2 | 4 | D | 4 | 0
Result:
Customer | Name | Sum
1 | A | 6
1 | B | 4
1 | C | 11
2 | A | 1
2 | B | 2
2 | D | 7
CodePudding user response:
The idea is to use windowed SUM to generate subgrp column and then use aggregate SUM to per Name, and subgrp.
Name | Val | Distinct_Group | subgrp
A | 1 | 1 | 1
A | 2 | 0 | 1
A | 3 | 0 | 1
B | 4 | 1 | 2
C | 5 | 1 | 3
C | 6 | 0 | 3
Query:
WITH cte AS (
SELECT *, SUM(Distinct_Group) OVER(ORDER BY <col_that_indicates_order>) AS subgrp
FROM tab
)
SELECT Name, subgrp, SUM(Val) AS Sum
FROM cte
GROUP BY Name, subgrp;