Column Must Appear in the GROUP BY Clause in PostgreSQL

You write a report query, run it, and PostgreSQL rejects it before reading a single row. On PostgreSQL 17 (16 prints the same text):

SELECT customer_id, status, count(*)
FROM orders
GROUP BY customer_id;

sql

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

Text

The query asks for one row per customer_id, but a customer can have orders in several statuses, and you haven’t said which status belongs on that one row. PostgreSQL won’t pick one for you. The SQLSTATE is 42803 (grouping_error), and the column is always reported as table_or_alias.column, so with FROM orders o you’d see column "o.status".

Quick fix checklist

Say what one output row stands for (“one row per customer”) and put exactly those columns in GROUP BY.
If the column is detail you want summarised, wrap it in an aggregate: count(*) FILTER (WHERE ...), string_agg, array_agg, max(created_at), bool_or.
If the column comes from a parent table you joined, group by that table’s primary key (GROUP BY c.id). PostgreSQL then allows c.name, c.email and the rest.
For “the latest order per customer”, use DISTINCT ON or row_number(), not GROUP BY plus max().
For every detail row plus a group total, use a window function: sum(total) OVER (PARTITION BY customer_id).
Check ORDER BY and HAVING as well, because the same rule applies to them.

Before you start

You need the full error text (the LINE and caret show which reference failed) and the table definitions, especially primary keys: \d orders in psql, or your migration files. The examples use PostgreSQL 16 and 17. The primary-key rule described below has applied since 9.1.

Why it happens

Think of the query in stages. FROM and WHERE produce a set of rows. GROUP BY customer_id then collapses every row with the same customer_id into a single group. From then on, the SELECT list, HAVING and ORDER BY are evaluated once per group, not once per original row.

For an expression to have one well-defined value per group, it must meet one of three conditions:

It is a grouping column (or built only from grouping columns), so it’s constant inside the group by definition.
It is inside an aggregate, which reduces the group’s many values to one: count, sum, max, string_agg, and so on.
It is functionally dependent on the grouping columns. PostgreSQL recognises exactly one kind of dependency: if the GROUP BY list contains the whole primary key of a table, any column of that table is allowed. A UNIQUE NOT NULL column doesn’t count, and neither does anything that only happens to be true of your current data.

The check happens at parse time, against the query and the schema. It never looks at the data. Even if every one of a customer’s orders shared the same status today, the query would still be rejected, because nothing in the schema guarantees it will tomorrow.

An aggregate with no GROUP BY at all turns the whole table into one group, so SELECT name, count(*) FROM customers fails with column "customers.name" must appear... for the same reason.

MySQL comparison. Before 5.7.5, MySQL accepted these queries and returned a value from some arbitrary row. Since 5.7.5 the ONLY_FULL_GROUP_BY SQL mode is on by default and gives error 1055 (“…is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”). MySQL detects more dependencies than PostgreSQL does (unique not-null keys and equalities in WHERE), so a query ported from MySQL can still fail here. MySQL’s escape hatch is ANY_VALUE(). PostgreSQL 16 added an any_value() aggregate too, which should only be used when every value in the group really is identical.

Step-by-step walkthrough

Step 1: Reproduce with a tiny dataset

Errors about grain are easier to reason about with rows you can see:

CREATE TABLE customers (
  id    int PRIMARY KEY,
  name  text NOT NULL,
  email text NOT NULL UNIQUE
);
CREATE TABLE orders (
  id          int PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO customers VALUES
  (7, 'Asha', 'asha@example.com'),
  (8, 'Ben',  'ben@example.com'),
  (9, 'Chen', 'chen@example.com');
INSERT INTO orders VALUES
  (101, 7, 'paid',     40.00, '2026-09-01'),
  (102, 7, 'refunded', 15.50, '2026-09-12'),
  (103, 8, 'paid',     99.00, '2026-09-03'),
  (104, 7, 'paid',     22.00, '2026-09-20');

sql

Customer 7 has orders in two statuses. That’s the ambiguity the error is protecting you from.

Step 2: Read the column and name the grain

The message tells you the first offending column, and the caret shows where it is. Then finish this sentence before you change anything: “each output row is one ___”. Your answer decides which fix is right. Grouping, aggregating and picking a row are different questions, and they give different results.

Step 3: Fix according to intent

If status is part of the grain (“one row per customer and status”), group by both:

SELECT customer_id, status, count(*)
FROM orders
GROUP BY customer_id, status;

sql

That returns three rows: (7, paid, 2), (7, refunded, 1), (8, paid, 1).

If the grain is one row per customer and status is detail, summarise it explicitly:

SELECT customer_id,
       count(*) AS orders,
       count(*) FILTER (WHERE status = 'refunded') AS refunds,
       string_agg(DISTINCT status, ', ' ORDER BY status) AS statuses,
       max(created_at) AS last_order_at
FROM orders
GROUP BY customer_id;

sql

If you need customer details, group by the customer’s primary key and select whatever you like from customers:

SELECT c.id, c.name, c.email,
       count(o.id) AS orders,
       coalesce(sum(o.total), 0) AS spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

sql

That returns Asha with 3 orders and 77.50, Ben with 1 and 99.00, and Chen with 0. Use count(o.id) rather than count(*) here so a customer with no orders gets 0, not 1.

Step 4: Pick a row instead of aggregating

“Show each customer’s latest order with its status” picks a whole row from each group, which is a different operation from aggregating. DISTINCT ON keeps the first row per key according to ORDER BY:

SELECT DISTINCT ON (customer_id)
       customer_id, id, status, created_at
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;

sql

The ORDER BY must start with the DISTINCT ON expressions. The trailing id DESC breaks ties so the result is deterministic. For portable SQL, or for “top 3 per customer”, use row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) in a subquery and filter on it.

Step 5: Keep detail rows with a window function

If the screen lists every order next to the customer’s total, you don’t want to collapse anything:

SELECT id, customer_id, total,
       sum(total) OVER (PARTITION BY customer_id) AS customer_spend
FROM orders;

sql

Every order stays, and each row carries 77.50 or 99.00. Window functions are computed after grouping, so they never trigger this error unless the query also has a GROUP BY.

Worked scenario

A dashboard endpoint lists customers with order counts. The developer grouped by email because it’s unique:

SELECT c.email, c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.email;

sql

ERROR:  column "c.name" must appear in the GROUP BY clause or be used in an aggregate function

Text

Diagnosis. email is UNIQUE NOT NULL, so logically each email has one name. But PostgreSQL only derives functional dependency from a primary key. A unique constraint can be dropped, and unique constraints allow multiple NULLs when the column is nullable, so PostgreSQL does not rely on them for this proof.

Fix. Group by the key that defines the grain:

SELECT c.email, c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

sql

If the report later needs totals from two child tables (orders and support tickets, say), aggregate each child in its own subquery or CTE keyed by customer_id and join those results to customers. Joining both children first multiplies rows, and the counts come out inflated.

Common mistake

Adding every column to GROUP BY until the error disappears. The query runs, but the grain changes. Add status and customer 7 splits into two rows, so the “orders per customer” chart double-counts that customer. Add o.id and every count(*) becomes 1. A silenced error with the wrong grain is worse than the error.

Wrapping the column in max() to make it legal. max(status) is the alphabetically greatest status, not the latest one. For customer 7 it returns refunded, though the most recent order (104) is paid. Worse, max(status), max(created_at) evaluates each aggregate independently, so the two values can come from different rows and describe an order that never existed. If you mean “the row with the latest timestamp”, use DISTINCT ON or row_number().

Reaching for any_value() to silence it. It’s fine when the values truly are identical within each group (a denormalised label you’ve verified). Used to make the error go away, it brings back MySQL’s old nondeterminism.

Verify the behavior

Prove the grain: no key should appear twice in the result. Wrap the report and look for duplicates:

SELECT customer_id, count(*)
FROM (
  SELECT c.id AS customer_id, c.name, count(o.id)
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id
) r
GROUP BY customer_id
HAVING count(*) > 1;

sql

Expected output is (0 rows). Then reconcile a total against the base table: SELECT sum(total) FROM orders should equal the sum of the report’s spend column (176.50 with the sample data). If it’s larger, a join is fanning out rows. If it’s smaller, a join or filter dropped some.

Interview exercise

“PostgreSQL accepts SELECT c.id, c.name, count(o.id) ... GROUP BY c.id but rejects the same query with GROUP BY c.email, even though email is UNIQUE NOT NULL. Why, and what does PostgreSQL do to keep the first query safe over time?”

Answer and reasoning

Every non-aggregated output expression needs one value per group. PostgreSQL proves that for an ungrouped column only through functional dependency on a primary key of that column’s table, as the SELECT documentation states. Unique constraints aren’t used for this proof, so c.name is rejected when grouping by email.

Because the query’s validity now rests on a constraint, PostgreSQL records that dependency for stored objects. If you create a view from the first query and then run ALTER TABLE customers DROP CONSTRAINT customers_pkey, the drop fails with view v depends on constraint customers_pkey on table customers unless you add CASCADE. A strong answer also points out that the fix should follow from the intended grain. Grouping by the primary key gives one row per customer. Adding name to GROUP BY would work here too, but it says less about what the query means.