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

Question

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 1

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