Trying to get results based on another table - aggregate functions are not allowed in WHERE

Question

I am trying to write a single query that will select all users based on their sum of donation from another table and order by the sum of their donations. But I am getting an error. I'm using postgres but I guess the same would apply to mysqql.

ERROR:  aggregate functions are not allowed in WHERE
LINE 8:  sum(donations.donation) = 400
         ^

users table

id |   username   |
1     Larry Page
2     Sergey Brin

donations table

id | user_id | donation |     date
1      1         100       2019-02-12
2      1         200       2019-02-13
3      2         500       2019-01-15
4      1         100       2019-04-10

Here's my query

select
    users.username, sum(donations.donation) as donation_sum
from
    users
inner join donations
    on users.id = donations.user_id
where
    sum(donations.donation) = 400
and
    donations.date between '2019-01-01' and '2019-12-31'
order by
    donation_sum

I was expecting the result to be this

username    |  donation_sum
Larry Page        400

Possible duplicate of [use mysql SUM() in a WHERE clause](https://stackoverflow.com/questions/3284409/use-mysql-sum-in-a-where-clause) — Nick, Apr 10 '19 at 12:41

TheWildHealer · Accepted Answer · 2019-04-10T12:45:32.190

2

To filter on aggregation results, you have to use the keyword HAVING:

select
    users.username, sum(donations.donation) as donation_sum
from
    users
inner join donations
    on users.id = donations.user_id
where
    donations.date between '2019-01-01' and '2019-12-31'
group by
    users.username
having
    sum(donations.donation) = 400
order by
    donation_sum

edited Apr 10 '19 at 12:45

answered Apr 10 '19 at 12:40

TheWildHealer

1,546
1
15
26

4

. . . `Having` without `GROUP BY` ? – Yogesh Sharma Apr 10 '19 at 12:40
Right, didn't even notice it was missing from the original query – TheWildHealer Apr 10 '19 at 12:42
. . . Your current syntax still not valid. – Yogesh Sharma Apr 10 '19 at 12:43
Then answer the question yourself and please, politely annoy someone's else answer's comments – TheWildHealer Apr 10 '19 at 12:45
1

@YogeshSharma: you can fix obvious typos yourself by simply editing the answer – Apr 10 '19 at 12:58
Which he did, changing all the formating in the same time, which is why I rolled it back. – TheWildHealer Apr 10 '19 at 13:02
Is there a way to do it without using having, maybe with sub queries? – Liga Apr 10 '19 at 13:16
There, is, though they will be less efficient. Why do you wish so? – TheWildHealer Apr 10 '19 at 13:20

Trying to get results based on another table - aggregate functions are not allowed in WHERE

1 Answers1