I have table that looks like:

I would like do create a table which shows the cumulative sum over time by each group, the group and the date.

Expected result:

I'm using the following query to get the total cumulative sum for all groups:

select to_char(date, 'YYYY-MM') month,
       sum(sum(amount))over(order by to_char(date, 'YYYY-MM')) sum_amount
from table
group by to_char(date, 'YYYY-MM')
order by 1

I tried to simply add the 'group' to this query, like this:

select to_char(date, 'YYYY-MM') month,
       sum(sum(amount))over(order by to_char(date, 'YYYY-MM'), group) sum_amount,
       group
from table
group by to_char(date, 'YYYY-MM'), group
order by 1

But the problem is that this last query still calculates the total amout and not for each individual group.

CodePudding user response：

You need a subquery to first aggregate per month and group. Then, the analytic SUM() can do the job. For example:

select dt, sum(a) over(partition by grp order by dt) as amt, grp
from (
  select dt, sum(amount) as a, grp
  from t
  group by dt, grp
) x
order by dt, grp

Result:

 DT      AMT  GRP 
 ------- ---- --- 
 2022-1  2    g1  
 2022-1  5    g2  
 2022-2  9    g1  
 2022-2  6    g2  
 2022-3  2    g3  

See running example at db<>fiddle.