SQL 错误：滥用聚合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/648083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL error: misuse of aggregate
提问by Piotr Byzia
SQLite version 3.4.0 What's wrong with aggregate functions? Additionally, I suspect that ORDER BY won't work as well. How to rewrite this?
SQLite 3.4.0 版聚合函数有什么问题？此外，我怀疑 ORDER BY 也不会起作用。如何重写这个？
sqlite> SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
...> FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
...> WHERE p1.id = i1.PDB_first_id
...> AND p2.id = i2.PDB_second_id
...> AND i1.id = i2.id
...> AND d1>100
...> AND d2>100
...> ORDER BY d1, d2;
SQL error: misuse of aggregate:
sqlite>
回答by Frederik Gheysels
When using an aggregate function (sum / count / ... ), you also have to make use of the GROUP BY clause.
使用聚合函数 (sum / count / ... ) 时，您还必须使用 GROUP BY 子句。
Next to that, when you want to filter on the result of an aggregate , you cannot do that in the WHERE clause, but you have to do that in the HAVING clause.
接下来，当您想过滤聚合的结果时，您不能在 WHERE 子句中执行此操作，但必须在 HAVING 子句中执行此操作。
SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
WHERE p1.id = i1.PDB_first_id
AND p2.id = i2.PDB_second_id
AND i1.id = i2.id
GROUP BY p1.domain_Id, p2.domain_Id
HAVING d1 > 100 AND d2 > 100
ORDER BY d1, d2;
回答by mFlorin
Short-version fix for this is:
对此的简短版本修复是：
When you're using function like COUNT/SUM, you need to use HAVINGinstead of WHERE.
当您使用类似 的函数时COUNT/SUM，您需要使用HAVING代替WHERE。
