// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
column "o.customer_id" must appear in the GROUP BY clause or be used in an aggregate function

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 WHERE instead of HAVING.
  • Queries written for MySQL's permissive grouping mode.

How to fix it

  1. 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;
  2. 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;
  3. To keep one whole row per group, use DISTINCT ON or a window function instead of GROUP BY.

    SELECT DISTINCT ON (customer_id) *
    FROM orders
    ORDER BY customer_id, created_at DESC;
  4. 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.