Why does the same query work in MySQL but not PostgreSQL?

MySQL historically allowed selecting non-grouped columns and returned an arbitrary value from each group. PostgreSQL follows the SQL standard and rejects this. Modern MySQL with ONLY_FULL_GROUP_BY enabled (the default since 5.7) behaves like PostgreSQL and raises a similar error.

Can I GROUP BY a column alias or position number?

Yes. PostgreSQL lets you GROUP BY an output column's alias or its 1-based position in the SELECT list, such as GROUP BY 1, 2. This is convenient but fragile — reordering the SELECT list silently changes the grouping, so prefer explicit column names in shared code.

Do I need to list every column in GROUP BY?

You must list every SELECT-list column that isn't inside an aggregate function. The one exception: if you GROUP BY a table's primary key, PostgreSQL lets you select that table's other columns without listing them, because the key determines their values.

Fix "column must appear in the GROUP BY clause" (Postgres)

sqlfmt▍

Fix "column must appear in the GROUP BY clause" (Postgres) — sqlfmt

The "column must appear in the GROUP BY clause" error means you put a column in your SELECT list that PostgreSQL can't reduce to a single value per group. Every column in a grouped query must either be listed in GROUP BY or wrapped in an aggregate function. That's the whole rule.

PostgreSQL reports it with SQLSTATE 42803 and a full message like column "orders.customer_name" must appear in the GROUP BY clause or be used in an aggregate function. It usually surprises people who added one extra column to an otherwise-working aggregate query. Below are the three fixes that cover every case, plus the primary-key exception that trips up even experienced users.

Why does PostgreSQL say "column must appear in the GROUP BY clause"?

GROUP BY collapses many rows into one row per group. Once rows are collapsed, a plain column reference is ambiguous: which of the grouped rows' values should it return? PostgreSQL refuses to guess. A column is only allowed in the SELECT, HAVING, or ORDER BY of a grouped query if the planner can prove it has exactly one value per group — meaning it's in GROUP BY or fed to an aggregate.

Here's the query that triggers it:

Broken: customer_name is neither grouped nor aggregated

SELECT customer_id, customer_name, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
-- ERROR:  column "orders.customer_name" must appear in the
--         GROUP BY clause or be used in an aggregate function

Fix 2: aggregate the varying column

SELECT customer_id,
       MAX(order_total)            AS biggest_order,
       string_agg(status, ', ')    AS all_statuses,
       COUNT(*)                    AS order_count
FROM orders
GROUP BY customer_id;

Legal: grouping by the primary key

-- customers.id is the PRIMARY KEY, so name/email are allowed
SELECT c.id, c.name, c.email, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Fix "column must appear in the GROUP BY clause"

Quick answer

FAQs

What is SQLSTATE 42803 in PostgreSQL?

Why does the same query work in MySQL but not PostgreSQL?

Can I GROUP BY a column alias or position number?

Do I need to list every column in GROUP BY?

PostgreSQL Relation Does Not Exist: Fix 42P01

MySQL Error 1054: Fix “Unknown Column”

SQL Server Invalid Object Name: Fix Msg 208

Why does PostgreSQL say "column must appear in the GROUP BY clause"?

Fix 1: Add the column to GROUP BY

Fix 2: Wrap the column in an aggregate function

Fix 3: Use a window function instead of GROUP BY

The primary-key exception

Gotchas: ORDER BY, HAVING, and aliases

Checklist: clearing the GROUP BY error

Wrapping up

Quick answer

FAQs

What is SQLSTATE 42803 in PostgreSQL?

Why does the same query work in MySQL but not PostgreSQL?

Can I GROUP BY a column alias or position number?

Do I need to list every column in GROUP BY?

Keep reading

PostgreSQL Relation Does Not Exist: Fix 42P01

MySQL Error 1054: Fix “Unknown Column”

SQL Server Invalid Object Name: Fix Msg 208

Why does PostgreSQL say "column must appear in the GROUP BY clause"?

Fix 1: Add the column to GROUP BY

Fix 2: Wrap the column in an aggregate function

Fix 3: Use a window function instead of GROUP BY

The primary-key exception

Gotchas: ORDER BY, HAVING, and aliases

Checklist: clearing the GROUP BY error

Wrapping up