I am interested in calculating a descending cumulative sum based on groups, however, I want the cumulative sum to be based on the grouping. In other words, I want the total sum within a group and cumulative sum to be between the groups, the order of group_id is important.

For example, using the sample data below:

group_id    Value
1           5
1           5
1           5
2           5
2           2
2           4
3           1
3           2
3           3

I am trying to get the following:

group_id    Value   CumSum
3           1       6
3           2       6
3           3       6
2           5       17
2           2       17
2           4       17
1           5       32
1           5       32
1           5       32

I have tried using a partition and then ordering on the group_id, however it does not appear to work, any assistance would be appreciated.

CREATE TABLE data (group_id INT, value INT)
insert into data values 
(1,5),
(1,5),
(1,5),
(2,5),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3);

-- My Attempt:
select *, sum(value) over(partition by group_id order by group_id, value) as cum_sum
from data;

CodePudding user response：

It's possible.
No partition in the SUM OVER, just an order by group_id.

select *
, [CumSum] = sum(value) over(order by group_id desc)
from data
order by group_id desc;

Test on db<>fiddle here

CodePudding user response：

In SQL Server you can use row_number() to find the first row in each group_id, and sum the value for the group. Then build a cumulative sum with the help of a CTE.

with cte as (
    select *
        , sum([value]) over (partition by group_id) cum_sum
        , row_number() over (partition by group_id order by group_id) group_row
    from #data
)
select group_id, [value]
    , sum(case when group_row = 1 then cum_sum else 0 end) over (order by group_id) cum_sum
from cte
order by group_id;