PostgreSQL- sum not recognized as aggregate function?

Question

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?

@Jack because I need running total of amount as explained in question. It has to add previous year total to next year and so on. With OVER, it can be done. — Andrius
– Andrius, Commented Mar 28, 2017 at 12:41
Ok - I never used OVER for things like this; I would have written it with an inner query — Jack
– Jack, Commented Mar 28, 2017 at 12:44
sum(sum(amount)) over ... should work too, but @klin's answer is easier (and already explains why). — pozs
– pozs, Commented Mar 28, 2017 at 13:02

klin · Accepted Answer · 2017-03-28 12:59:26Z

3

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)

edited Mar 28, 2017 at 12:59

answered Mar 28, 2017 at 12:46

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

klin Over a year ago

Because I've forgotten DISTINCT, sorry. The answer edited.

Collectives™ on Stack Overflow

PostgreSQL- sum not recognized as aggregate function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related