3

I have a table like this:

month region    value
4/1/20  eu-west-2   110
3/1/20  eu-west-2   30
2/1/20  eu-west-2   13
2/1/20  us-west-2   2
1/1/20  us-west-2   242
4/1/20  us-west-2   240

I want to find the cumulative sum for the column 'value' for each region each month. For eg: Using the above example answer should be:

month region cumulative_sum
4/1/20 eu-west-2  153
3/1/20 eu-west-2  43
2/1/20 eu-west-2  13
4/1/20 us-west-2  484
2/1/20 us-west-2  244
1/1/20 us-west-2  242

I'm able to successfully write a query when I'm only finding cumulative sum for each month, but it's not working when I add region to it:

select
month, sum(value) over (order by month rows unbounded preceding) as cumulative_sum
from table

but when I do:

  select
    month, region, sum(value) over (order by month,region rows unbounded preceding) as cumulative_sum
    from table

it's giving wrong results.

Please help.

GMB
  • 216,147
  • 25
  • 84
  • 135
user10096621
  • 225
  • 2
  • 4
  • 16

2 Answers2

4

Assuming that month is a column of a date-like datatype, you can do:

select
    month,
    region,
    sum(value) over(
        partition by region, date_trunc('month', month)
        order by month
    ) cumulative_sum
from mytable

The partition by clause of the window sum() puts together rows that belong to the same month and region. Everytime the region changes or a new month starts, the sum resets.

GMB
  • 216,147
  • 25
  • 84
  • 135
2

You are quite close, except you are missing the partition by:

select month, region,
       sum(value) over (partition by region order by month rows unbounded preceding) as cumulative_sum
from table
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786