Search code examples
mysqlsqlaggregate-functions

How to avoid error "aggregate functions are not allowed in WHERE"

This sql code throws an

aggregate functions are not allowed in WHERE

SELECT o.ID ,  count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID 
WHERE count(p.CAT) > 3
GROUP BY o.ID;

How can I avoid this error?

Solution

  • Replace WHERE clause with HAVING, like this:

    SELECT o.ID ,  count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID 
GROUP BY o.ID
HAVING count(p.CAT) > 3;

    HAVING is similar to WHERE, that is both are used to filter the resulting records but HAVING is used to filter on aggregated data (when GROUP BY is used).