D-Lef D-Lef - 1 year ago 87
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download