PostgreSQL: 'column must appear in the GROUP BY clause or be used in an aggregate function' (42803)

ERROR: column "c.city" must appear in the GROUP BY clause or be used in an aggregate function (SQLSTATE 42803) means your SELECT list names a column that is neither grouped nor wrapped in an aggregate such as sum() or max(). PostgreSQL cannot know which of the group's values to show, so it refuses the query.

Add the column to GROUP BY, or group by the table's primary key, which lets PostgreSQL accept every other column of that table without listing them. If you really want one value out of many, say which one with an aggregate.

The error

SELECT c.name, c.city, sum(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
psql:/tmp/p1.sql:21: ERROR:  column "c.city" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT c.name, c.city, sum(o.total) AS spent
                       ^

Why it happens

GROUP BY c.name collapses all rows with the same name into one output row. sum(o.total) knows how to turn many order rows into one number, but c.city is a plain column: if two customers called Maria Garcia lived in different cities, there would be two candidate values for one cell. The SQL standard forbids that, and PostgreSQL enforces it at parse time, before it reads a single row. The caret under c.city points at the offending column in your statement.

Note what PostgreSQL did not complain about: c.name is grouped and o.total is inside sum(), so both are fine. The rule is per column, and the message names the column to look at, here qualified with the alias c.

The fix

SELECT c.name, c.city, sum(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name, c.city
ORDER BY c.name;
     name     |  city   | spent
--------------+---------+-------
 David Chen   | Toronto | 99.90
 Maria Garcia | Lisbon  | 65.50
(2 rows)

Listing every non-aggregated column is the portable fix. When the columns all come from one table, grouping by that table's primary key is shorter and also more correct, because two different customers with the same name stay apart. PostgreSQL detects that name and city depend on the key and accepts them:

SELECT c.name, c.city, sum(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.name;
     name     |  city   | spent
--------------+---------+-------
 David Chen   | Toronto | 99.90
 Maria Garcia | Lisbon  | 65.50
(2 rows)

That shortcut works for the primary key, not for an ordinary column such as name. From .NET the same failure arrives as a PostgresException whose SqlState is 42803.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2, with two tables, customers (id, name, city) and orders (id, customer_id, total), filled with two fictional customers and three orders. The script ran through psql -a -f inside the container; the failing query grouped by c.name only, and both fixed queries returned the two rows shown. A second run with \set VERBOSITY verbose printed the code as ERROR: 42803.

Frequently asked

What does 'must appear in the GROUP BY clause or be used in an aggregate function' mean?

A column in the SELECT list is neither grouped nor aggregated, so one output row could have several values for it. PostgreSQL rejects the query with SQLSTATE 42803 before running it.

Can I GROUP BY the primary key instead of every column?

Yes. When you group by a table's primary key, PostgreSQL accepts the other columns of that table in the SELECT list because they depend on the key. It does not work for an ordinary, non-unique column.

How do I show one value per group for a column I do not want to group by?

Wrap it in an aggregate that says which value you want, for example max(c.city) or min(c.city). PostgreSQL then accepts the query because the column is no longer ungrouped.

More decoded errors in the Fixes category. If the same query then fails on a column name, see column does not exist (42703).