PostgreSQL- sum not recognized as aggregate function? -

i need have running amount of transactions per year. if total amount @ 2015 150 , @ 2016, total amount 90. means @ 2016 running amount 240. , on.

so have data:

create table transactions2(year_num int, amount int);  insert transactions2 values(2015, 100); insert transactions2 values(2015, 50); insert transactions2 values(2016, 90); insert transactions2 values(2017, 100); insert transactions2 values(2019, 200);  select year_num, count(amount), sum(amount)  on (order year_num)  transactions2  group year_num order year_num; 

if run select sql, get:

error:  column "transactions2.amount" must appear in group clause or used in aggregate function line 9: select year_num, count(amount), sum(amount) on (order y...                                             ^  ********** error **********  error: column "transactions2.amount" must appear in group clause or used in aggregate function sql state: 42803 character: 328 

but have amount in sum function. why not working? if wrap sum(count(sum)), works, not need have sum of count, need sum.

do need write inner select this?

in expression:

sum(amount) on (order year_num)  

sum() not simple aggregate, it's window function.

you want use both count() , sum() window functions:

select distinct year_num, count(amount) on w, sum(amount) on w transactions2  window w (order year_num) order year_num;   year_num | count | sum  ----------+-------+-----      2015 |     2 | 150      2016 |     3 | 240      2017 |     4 | 340      2019 |     5 | 540 (4 rows)