“Aggregate Functions Are Not Allowed in WHERE” — SQL Fix
WHERE filters rows before grouping, so aggregates do not exist yet. Use HAVING.
- 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()
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 in WHERE, 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 runs
after GROUP BY, which is exactly where an aggregate filter belongs.
The fix
Filter rows with WHERE and filter groups with
HAVING. They often appear together: WHERE trims
the input first, then HAVING trims the grouped result.
select category_id, count(*) as products
from products
where count(*) > 3
group by category_id
Running it raises
WHERE clause cannot contain aggregates.
select category_id, count(*) as products
from products
where discontinued = false
group by category_id
having count(*) > 3
If the condition talks about a single row's columns, it belongs in
WHERE. If it talks about an aggregate of the group
(count, sum, avg), it belongs in
HAVING. Keep ordinary input-row conditions in
WHERE for clarity and portability; whether a particular engine
accepts or rewrites a similar HAVING condition varies.
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() |
Related errors
- Fix “Column Must Appear in the GROUP BY Clause” in SQL
A selected column is neither grouped nor aggregated, so this engine cannot derive one value for the group.
- Fix “Window Functions Are Not Allowed in WHERE” in SQL
Window functions are evaluated after WHERE, so you cannot filter on one directly.
Learn more
Related
Turn rows into counts.
COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.
COUNT, SUM, AVG, GROUP BY, and HAVING.
Practice aggregate reports, HAVING, and answer shape.
Avoid truncating fractional results.
Calculate totals, averages, and extremes.