D-Lef D-Lef - 1 month ago 10
MySQL Question

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?

Answer

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