这是表结构
供体(供体ID,名称)
捐赠(ID,捐赠者ID,金额)
这是我的查询:
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;
这是错误:
ORA-00934:此处不允许使用组功能
00934. 00000-“此处不允许使用组功能”
当我删除sum(donation)我如何正确集成它?请帮忙。
最佳答案
您不能将聚合函数放在where
子句中。您需要一个having
子句:
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;
但是,您应该学习使用正确的
join
语法和表别名: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;