Fix “Aggregate Functions Are Not Allowed in WHERE” in SQL
WHERE filters rows before grouping, so aggregates do not exist yet. Use HAVING.
The symptom
You try to keep only the groups that meet a total, such as categories with more than 3 products or customers who spent over $100, by putting the aggregate inWHERE, and the database errors with something like"aggregate functions are not allowed in WHERE".
Why it happens
WHERE is evaluated before rows are grouped, so at that point the groups and their COUNT, SUM, and AVG values do not exist yet. HAVING runsafterGROUP BY, which is exactly where an aggregate filter belongs.
The fix
Filter rows with WHERE and filter groups withHAVING. They often appear together: WHERE trims the input first, then HAVING trims the grouped result.
If the condition talks about a single row's columns, it belongs inWHERE. If it talks about an aggregate of the group (count, sum, avg), it belongs inHAVING. Putting a plain row filter in HAVING works but is slower because it filters late instead of early.
How other engines word it
| Engine | Message |
|---|---|
| DuckDB | Binder Error: WHERE clause cannot contain aggregates! |
| PostgreSQL | aggregate functions are not allowed in WHERE |
| SQL Server | An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. |
| MySQL | Invalid use of group function |
| SQLite | misuse of aggregate: count() |