grouping_error aggregate function calls cannot be nested — 42803

Symptoms

An aggregate was placed directly inside another aggregate, e.g. sum(avg(x)). SQL does not allow nested aggregation in a single query level, so PostgreSQL raises SQLSTATE 42803.

• The caret points at the inner aggregate call.
• Usually a logic error or an attempt to aggregate an already-aggregated value.
• The fix is almost always a subquery or window function.

What the server log shows

ERROR:  aggregate function calls cannot be nested
LINE 1: SELECT sum(avg(amount)) FROM orders;
                   ^

Why PostgreSQL raises this — what the manual says

As Section 4.2.7 Aggregate Expressions explains:

An aggregate function’s arguments cannot themselves contain another aggregate call, because the inner aggregate would have no well-defined set of rows to operate over; rewrite the query (often with a subquery) so each aggregate is computed at a single level.

Aggregates collapse a set of rows into one value at a single query level. Nesting one inside another would require two aggregation passes in the same level, which the SQL standard and PostgreSQL forbid — hence 42803.

Common causes

• Trying to aggregate the result of an aggregate (e.g. average of per-group sums) in one query.
• Misplaced parentheses producing accidental nesting.

How to fix it

1. Aggregate in two steps with a subquery: SELECT avg(s) FROM (SELECT sum(amount) AS s FROM orders GROUP BY customer_id) t;.
2. Use a window function when you need per-row aggregates feeding another calculation.
3. Check parentheses to ensure you did not nest by mistake.

Related & next steps

• SQLSTATE 42803 — Grouping Error (full reference)
• Message: column must appear in the GROUP BY clause …
• Glossary: Window function

Reference: PostgreSQL 18 Section 4.2.7 “Aggregate Expressions”.