CODEWITHSUNDEEP
sqlsql-servert-sqlteradata
nwaldo
nwaldo

Reputation: 187

Is it possible to calculate the total sum within partition and cumulative sum between partitions?

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;

Upvotes: 0

Views: 1569

Answers (3)

dnoeth
dnoeth

Reputation: 60462

@LukStorms solution will not work as expected in Teradata, because it doesn't support RANGE and thus not Standard SQL's default of RANGE UNBOUNDED PRECEDING when there's ORDER BY (it defaults to a GROUP Sum instead).

Different logic to Dale K's answer:

WITH cte AS
 (
   SELECT group_id, value
      -- cumulative sum per group
     ,SUM(value) OVER (ORDER BY group_id DESC ROWS UNBOUNDED PRECEDING) AS cumsum
   FROM data
 )
SELECT group_id, value
   -- max per group
  ,MAX(cumsum) OVER (PARTITION BY group_id)
FROM cte;

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

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;
group_id value CumSum
3 1 6
3 2 6
3 3 6
2 5 17
2 2 17
2 4 17
1 2 23
1 3 23
1 1 23

Test on db<>fiddle here

Upvotes: 2

Dale K
Dale K

Reputation: 27202

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;

Upvotes: 1

Related Questions