SQL滚动和产生意外错误

Question

我试图找到按月划分并按Id排序的列的累积和。我得到这个错误。

column "amount_paid" must appear in the GROUP BY clause or be used in an aggregate function

的代码

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

Answer 1

我怀疑您想要每个id的每月金额的累积总和。如果是这样，则需要在窗口函数中使用聚合函数:

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