CODEWITHSUNDEEP
sqlpostgresql
Liga
Liga

Reputation: 3439

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

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

Upvotes: 1

Views: 207

Answers (1)

TheWildHealer
TheWildHealer

Reputation: 1616

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

Upvotes: 2

Related Questions