Is there a way to group by running total value?

Question

I have a dataset as follows, where the records, identified by ID, are grouped by GroupID, and the order within the group is Group Order. They have various Value attributes.

;WITH CTE AS
( SELECT *
  FROM (VALUES
(1, 1, 1, 20)
(2, 1, 2, 31)
(3, 1, 3, 20)
(4, 2, 1, 51)
(5, 2, 2, 20)
(6, 2, 3, 10)
(7, 3, 1, 15)
(8, 3, 2, 15)
) AS MyValues(ID, GroupID, GroupOrder, Value)
)
SELECT *
FROM CTE

Is there a way to group by running total value, so to add another field, TotalGrouping, which would read A for every item where the running total is less than 50, and B where the running total exceeds 50. I have tried to use SUM OVER(), but I can't generate the required result. Any guidance is appreciated, thank you.

-

ID	TotalGrouping
1	A
2	B
3	B
4	B
5	B
6	B
7	A
8	A

Answer 1

Probably you were very near to the solution. Once you've computed the running sum, you can use a CASE expression to set 'A' or 'B' accordingly.

SELECT ID, 
       CASE WHEN SUM(Value_) OVER(PARTITION BY GroupID ORDER BY GroupOrder) >= 50 
            THEN 'B'
            ELSE 'A'
       END AS TotalGrouping
FROM tab

Output:

ID	TotalGrouping
1	A
2	B
3	B
4	B
5	B
6	B
7	A
8	A

Check the demo here.