List all of the donors who have donated a total amount greater than something

Question

This is table structure

Donor (donorID, name)

Donation (ID, donorID, amount)

Here is my query :

SELECT donor.name, SUM(donation.amount) "Total"
FROM donor, donation
WHERE donor.donorID = donation.donorID
AND SUM(donation.amount) > 1000;
GROUP BY donor.name
ORDER BY SUM(donation.amount) DESC;

Here is the error:

ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here"

There is not error when i remove the sum(donation) < 1000 How do i integrate it without error? Please help.

Answer 1

You cannot put an aggregation function in the where clause. You need a having clause:

SELECT donor.name, SUM(donation.amount) "Total"
FROM donor, donation
WHERE donor.donorID = donation.donorID
GROUP BY donor.name
HAVING SUM(donation.amount) > 1000
ORDER BY SUM(donation.amount) DESC;

However, you should learn to use proper join syntax and table aliases:

SELECT d.name, SUM(dn.amount) as "Total"
FROM donor d JOIN
     donation dn
     ON d.donorID = dn.donorID
GROUP BY d.name
HAVING SUM(dn.amount) > 1000
ORDER BY SUM(dn.amount) DESC;

Answer 2

You need to use a HAVING clause like so:

SELECT donor.name, SUM(donation.amount) "Total"
FROM donor, donation
WHERE donor.donorID = donation.donorID
GROUP BY donor.name
HAVING SUM(donation.amount) > 1000
ORDER BY SUM(donation.amount) DESC;

Also, as Marc B pointed out, the semi-colon is in an incorrect place terminating the query.