PostgreSQL 42803: column must appear in the GROUP BY clause
A query with GROUP BY or an aggregate selects a column that is neither grouped nor aggregated, so there is no single value to show per group.
- SQLSTATE
42803- Name
grouping_error
What it means
With GROUP BY (or any aggregate such as count(*)), each output row stands for a group of input rows. Every selected column must then be one of the grouping columns, be inside an aggregate, or be functionally dependent on the grouped primary key. Otherwise its value would differ within the group and PostgreSQL won't pick one arbitrarily, so it raises 42803 (grouping_error).
MySQL with ONLY_FULL_GROUP_BY turned off allows such queries, so this often appears when moving queries to PostgreSQL. The same code covers aggregates in the wrong place, such as aggregate functions are not allowed in WHERE.
Common causes
- A selected column missing from
GROUP BY. - Grouping by an expression but selecting the underlying column.
- An aggregate used in
WHEREinstead ofHAVING. - Queries written for MySQL's permissive grouping mode.
How to fix it
-
Add the column to
GROUP BY, or aggregate it.SELECT o.customer_id, count(*) AS orders, max(o.created_at) AS last_order FROM orders o GROUP BY o.customer_id;
-
Group by the table's primary key; its other columns are then allowed in the select list.
SELECT c.id, c.name, count(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id;
-
To keep one whole row per group, use
DISTINCT ONor a window function instead ofGROUP BY.SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC;
-
Filter on aggregates with
HAVING.SELECT customer_id FROM orders GROUP BY customer_id HAVING count(*) > 5;
Reproduce it safely
SELECT x, count(*) FROM (VALUES (1, 'a')) v(x, y) GROUP BY y; -- ERROR: column "v.x" must appear in the GROUP BY clause ...
Related errors
42702column reference is ambiguous21000more than one row returned by a subquery8120SQL Server: Column not in GROUP BY or aggregate1055MySQL: Expression isn't in GROUP BYORA-00979Oracle: not a GROUP BY expressionORA-00937Oracle: not a single-group group function
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42703column does not exist42883function does not exist
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.