grouping_error aggregate functions are not allowed in WHERE — 42803

Symptoms

An aggregate function (such as SUM, COUNT, MAX) was used in a WHERE clause. Aggregates filter grouped results, not individual rows, so PostgreSQL raises SQLSTATE 42803 (grouping_error).

• An aggregate appears in WHERE instead of HAVING.
• WHERE filters rows before grouping; aggregates run after.
• Common when trying to filter on a computed total.

What the server log shows

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT * FROM orders WHERE sum(amount) > 100

Why PostgreSQL raises this — what the manual says

Section 2.7 Aggregate Functions:

“Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates.”

Query evaluation applies WHERE to individual rows before grouping and aggregation occur. An aggregate has no value at that stage, so PostgreSQL rejects it in WHERE with 42803.

Common causes

• Filtering on an aggregate in WHERE rather than HAVING.
• Confusing per-row filtering with post-group filtering.
• Porting SQL that allowed it in another dialect.

How to fix it

1. Move the aggregate condition to HAVING: … GROUP BY … HAVING sum(amount) > 100.
2. Use a subquery/CTE to compute the aggregate, then filter on it.
3. Filter raw-row conditions in WHERE, aggregated conditions in HAVING.

Related & next steps

• SQLSTATE 42803 — Grouping Error (full reference)
• Message: window functions are not allowed in WHERE
• Glossary: Aggregate function

Reference: PostgreSQL 18 Section 2.7 “Aggregate Functions”.