SQL rolling sum giving an unexpected error
I am trying to find the cumulative sum of a column partitioned by month and ordered by Id. I am getting this error.
column "amount_paid" must appear in the GROUP BY clau开发者_如何学Cse or be used in an aggregate function
The code
select
month,
Id,
SUM(amount_paid) OVER(PARTITION BY month ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
from table
where month >= '2022-01-01'
and Id between 0 and 12
group by month,Id
order by month,Id
Data
month | Id | amount paid
2022-01-01 | 1 | 5866
2022-01-01 | 2 | 8466
2022-01-01 | 3 | 6816
2022-02-01 | 1 | 855
2022-02-01 | 2 | 9821
2022-02-01 | 3 | 3755
I suspect that you want a cumulative sum of the monthly sums for each id
. If so, then you need to use an aggregate function within the window function:
select month, id,
SUM(amount_paid) AS amount_paid_this_month_for_this_id,
SUM(SUM(amount_paid)) OVER(PARTITION BY id ORDER BY month) AS amount_paid_so_far_for_this_id
from table
where month >= '2022-01-01'
and Id between 0 and 12
group by month, id
order by month, id
精彩评论