开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜