CODEWITHSUNDEEP
postgresqlaggregate-functionscumulative-sum
Andrius
Andrius

Reputation: 21168

PostgreSQL- sum not recognized as aggregate function?

I need to have running amount of transactions per year. So if total amount at 2015 was 150 and at 2016, total amount was 90. It means at 2016 running amount is 240. And so on.

So I have this data:

CREATE table transactions2(year_num int, amount int);

insert into transactions2 values(2015, 100);
insert into transactions2 values(2015, 50);
insert into transactions2 values(2016, 90);
insert into transactions2 values(2017, 100);
insert into transactions2 values(2019, 200);

SELECT year_num, count(amount), sum(amount) 
OVER (ORDER BY year_num) 
FROM transactions2 
GROUP BY year_num ORDER BY year_num;

If I run this select SQL, I get:

ERROR:  column "transactions2.amount" must appear in the GROUP BY clause or be used in an aggregate function
LINE 9: SELECT year_num, count(amount), sum(amount) OVER (ORDER BY y...
                                            ^

********** Error **********

ERROR: column "transactions2.amount" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 328

But I have amount in sum function. So Why it is not working? If I wrap it like sum(count(sum)), then it works, but I do not need to have sum of count, I just need sum.

Do I need to write inner select just for this?

Upvotes: 1

Views: 2438

Answers (1)

klin
klin

Reputation: 121774

In the expression:

sum(amount) OVER (ORDER BY year_num)

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

You probably want to use both count() and sum() as window functions:

SELECT DISTINCT year_num, count(amount) over w, sum(amount) over w
FROM transactions2 
WINDOW w as (ORDER BY year_num)
ORDER BY year_num;

 year_num | count | sum 
----------+-------+-----
     2015 |     2 | 150
     2016 |     3 | 240
     2017 |     4 | 340
     2019 |     5 | 540
(4 rows)

Upvotes: 3

Related Questions