grouping_error column “…” must appear in the GROUP BY clause or be used in an aggregate function — 42803

Symptoms

A query mixes aggregated and non-aggregated output columns, but a column in the SELECT (or HAVING/ORDER BY) is neither inside an aggregate nor listed in GROUP BY. PostgreSQL cannot decide which row’s value to return, so it raises SQLSTATE 42803 (grouping_error).

• Appears the moment you add an aggregate like count(*) or sum().
• The caret points at the ungrouped column.
• Other engines (e.g. older MySQL) accepted this; PostgreSQL does not.

What the server log shows

ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT status, count(*) FROM orders;
               ^

Why PostgreSQL raises this — what the manual says

Section 7.2.3 The GROUP BY and HAVING Clauses:

“In general, if a table is grouped, columns that are not listed in GROUP BY cannot be referenced except in aggregate expressions.”

Once a query is grouped, each output row represents a set of input rows. A bare column would have many possible values per group, which is ambiguous. PostgreSQL only allows it when the column is in GROUP BY or is functionally dependent on the table’s primary key that is grouped.

Common causes

• Selecting extra columns alongside an aggregate without grouping them.
• Adding a column to SELECT but forgetting to extend GROUP BY.
• Expecting MySQL-style implicit grouping behaviour.

How to fix it

1. Add the column to GROUP BY: SELECT status, count(*) FROM orders GROUP BY status;.
2. Wrap it in an aggregate if you want one value per group: max(created_at), min(price).
3. Group by the primary key to legally select dependent columns: GROUP BY o.id lets you select other o.* columns.

Related & next steps

• SQLSTATE 42803 — Grouping Error (full reference)
• Message: aggregate function calls cannot be nested
• Glossary: Aggregate function

Reference: PostgreSQL 18 Section 7.2.3 “GROUP BY and HAVING”.