column must appear in the GROUP BY clause or be used in an aggregate function

What it means

GROUP BY collapses many rows into one row per group. Every column you output from a group must have one value per group: either it’s one of the grouping columns, or it’s inside an aggregate such as sum(), count() or max(). A bare column that could differ between the rows of a group has no single answer, so PostgreSQL refuses rather than picking one at random.

There’s one exception: if you group by a table’s primary key, every other column of that table is allowed, because the key decides them (a functional dependency). A UNIQUE column, even a NOT NULL one, doesn’t count.

The same rule applies with no GROUP BY at all once there’s an aggregate: SELECT count(*), total FROM invoices fails too.

Common causes

1. Joining for a name and grouping by the foreign key. GROUP BY i.customer_id with c.name in the select list: PostgreSQL doesn’t infer that one customer id means one name.
2. Grouping by a unique column that isn’t the primary key, such as email.
3. Ordering by a column you didn’t group: ORDER BY created_at in a grouped query.
4. Wanting a whole row per group (“the latest invoice for each customer”), which GROUP BY can’t express. That needs DISTINCT ON or a window function.
5. SQL written for MySQL with ONLY_FULL_GROUP_BY off, where the server quietly returns any one value from the group.

How to fix it

Group by the primary key

If the extra columns come from one table, group by that table’s primary key and you can select any of its columns:

SELECT c.id, c.name, c.email, sum(i.total)
FROM invoices i
JOIN customers c ON c.id = i.customer_id
GROUP BY c.id;

Add the column to GROUP BY

If the column really is part of what you’re grouping by, list it:

SELECT i.customer_id, c.name, sum(i.total)
FROM invoices i
JOIN customers c ON c.id = i.customer_id
GROUP BY i.customer_id, c.name;

Aggregate it

Decide which value you want and say so:

SELECT customer_id,
       sum(total)        AS spent,
       max(created_at)   AS last_invoice,
       string_agg(DISTINCT status, ', ') AS statuses
FROM invoices
GROUP BY customer_id
ORDER BY max(created_at) DESC;

On PostgreSQL 16 and later, any_value(col) returns one value from the group when you know they’re all the same and don’t care which; earlier versions report function any_value(numeric) does not exist.

One whole row per group: DISTINCT ON

For “the latest invoice per customer”, with all its columns:

SELECT DISTINCT ON (customer_id) customer_id, id, total, created_at
FROM invoices
ORDER BY customer_id, created_at DESC;

DISTINCT ON keeps the first row of each group in the ORDER BY order. Its expressions must come first in the ORDER BY, or you get SELECT DISTINCT ON expressions must match initial ORDER BY expressions.

Keep every row: a window function

If you want each row and a group total beside it, don’t group at all:

SELECT customer_id, total, sum(total) OVER (PARTITION BY customer_id) AS customer_total
FROM invoices;

Reproduce it

On PostgreSQL 18.6, with two customers and three invoices:

CREATE TABLE customers (id int PRIMARY KEY, name text, email text UNIQUE NOT NULL);
CREATE TABLE invoices (id int PRIMARY KEY, customer_id int REFERENCES customers, total numeric, created_at date);
INSERT INTO customers VALUES (1, 'Ada', 'ada@example.com'), (2, 'Grace', 'grace@example.com');
INSERT INTO invoices VALUES (1, 1, 100, '2026-09-01'), (2, 1, 50, '2026-10-01'), (3, 2, 70, '2026-10-02');

SELECT i.customer_id, c.name, sum(i.total) FROM invoices i JOIN customers c ON c.id = i.customer_id GROUP BY i.customer_id;

ERROR:  column "c.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT i.customer_id, c.name, sum(i.total) FROM invoices i J...
                              ^

Grouping by c.id, the primary key, works:

 id | name  | sum 
----+-------+-----
  1 | Ada   | 150
  2 | Grace |  70
(2 rows)

Grouping by c.email, which is UNIQUE NOT NULL, fails with the same error for c.name. An ungrouped column in ORDER BY, and an aggregate without GROUP BY:

ERROR:  column "invoices.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...otal) FROM invoices GROUP BY customer_id ORDER BY created_at...
                                                             ^

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

PostgreSQL 14 to 17 give the same errors; any_value() works on 16, 17 and 18 only.

In Inlet

When the statement fails, Inlet shows the error at the position the server reports, so you see which column it means. With your own Anthropic API key, Ask Claude (⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.