Sum Values Based on Specific Value in a Column Within Group-CodePudding

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;