SQL 错误:滥用聚合 [英] SQL error: misuse of aggregate

问题描述

SQLite 3.4.0 版聚合函数有什么问题?此外，我怀疑 ORDER BY 也不会起作用.如何重写这个?

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> 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>

推荐答案

在使用聚合函数(sum/count/... )时，您还必须使用 GROUP BY 子句.

When using an aggregate function (sum / count / ... ), you also have to make use of the GROUP BY clause.

接下来，当你想过滤一个聚合的结果时，你不能在 WHERE 子句中这样做，但你必须在 HAVING 子句中这样做.

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.

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;

这篇关于SQL 错误:滥用聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！