Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
PostgreSQL's "column must appear in the GROUP BY clause" error (42803) fires when a SELECT column isn't grouped or aggregated. Three fixes, with examples.
Quick answer
PostgreSQL raises "column must appear in the GROUP BY clause or be used in an aggregate function" (error 42803) when a column in your SELECT list is neither named in GROUP BY nor wrapped in an aggregate like COUNT() or SUM(). Fix it by adding the column to GROUP BY, aggregating it, or replacing GROUP BY with a window function.
FAQs
What is SQLSTATE 42803 in PostgreSQL?
42803 is the grouping_error code PostgreSQL returns when a query mixes aggregated and non-aggregated columns illegally — most often "column must appear in the GROUP BY clause or be used in an aggregate function." It signals that a selected column has no single value per group. The fix is to group it, aggregate it, or use a window function.
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.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
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_countFROM ordersGROUP BY customer_id;-- ERROR: column "orders.customer_name" must appear in the-- GROUP BY clause or be used in an aggregate function
We grouped by customer_id but also selected customer_name, which Postgres can't tie to a single grouped value. MySQL with ONLY_FULL_GROUP_BY disabled would silently pick an arbitrary row here; PostgreSQL, SQL Server, and standard SQL all reject it. PostgreSQL is being correct, not pedantic.
Fix 1: Add the column to GROUP BY
If you want customer_name in the output and it doesn't vary within a customer, just group by it too. Grouping by both customer_id and customer_name produces the same groups (one per customer) while making the column legal.
Fix 1: group by every non-aggregated column
SELECT customer_id, customer_name, COUNT(*) AS order_countFROM ordersGROUP BY customer_id, customer_name;
This is the right fix when the extra column is descriptive (a name, a category label, a status) that's constant within each group.
Fix 2: Wrap the column in an aggregate function
When the column genuinely varies within a group, decide which value you want and apply an aggregate: MAX(), MIN(), SUM(), AVG(), or string_agg(). Postgres now knows it collapses to one value.
Fix 2: aggregate the varying column
SELECT customer_id, MAX(order_total) AS biggest_order, string_agg(status, ', ') AS all_statuses, COUNT(*) AS order_countFROM ordersGROUP BY customer_id;
Reach for string_agg() when you want to keep every value of a varying text column flattened into one cell instead of throwing rows away.
Fix 3: Use a window function instead of GROUP BY
Sometimes you don't actually want fewer rows — you want each original row alongside a group total. GROUP BY can't do that; a window function can. OVER (PARTITION BY ...) computes the aggregate per group but keeps every row, so no column has to "appear in GROUP BY" because there's no GROUP BY at all.
If you reach for window functions often, formatting the OVER clause cleanly matters a lot — see our guide to formatting SQL window functions for a readable house style.
The primary-key exception
Since version 9.1, PostgreSQL is smarter than the strict standard: if you GROUP BY a table's primary key, you may select any other column of that table without listing it. Postgres knows the primary key functionally determines every other column, so each group is exactly one row. The PostgreSQL SELECT documentation describes this functional-dependency rule.
Note this only works for the table's real primary key, not just any unique-looking column, and not across a join's other tables. When in doubt, group by the key explicitly.
Gotchas: ORDER BY, HAVING, and aliases
The rule extends past SELECT. These are the cases that produce the same 42803 error from a less obvious spot:
ORDER BY a non-grouped column — sorting by something that has no single value per group fails the same way. Order by an aggregate or a grouped column instead.
HAVING referencing a raw column — HAVING filters groups, so it must use aggregates, not bare columns. Use WHERE for row-level filters before grouping.
GROUP BY an output alias — you can GROUP BY a SELECT alias or its 1-based position in Postgres, but mismatching them against the SELECT list reintroduces the error. Keep the lists aligned.
Checklist: clearing the GROUP BY error
Read the error — it names the exact column that's unaccounted for.
Decide the column's role: is it constant within the group, or does it vary?
Constant within the group → add it to GROUP BY.
Varies within the group → wrap it in MAX/MIN/SUM/string_agg, picking the value you actually want.
Want every row plus a group total → drop GROUP BY and use a window function.
Check ORDER BY and HAVING for the same unaccounted column.
Re-run; if it still fails, the error names the next offending column — repeat.
Wrapping up
The "column must appear in the GROUP BY clause" error always comes down to one column with no single value per group — group it, aggregate it, or switch to a window function. When a misplaced column hides inside a long, unformatted query, the offender is hard to spot; sqlfmt formats your SELECT and GROUP BY lists onto clean, aligned lines so you can see at a glance which columns are grouped and which aren't.
Fix 3: keep every row with a windowed count
SELECT order_id, customer_id, customer_name, COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_countFROM orders;
Legal: grouping by the primary key
-- customers.id is the PRIMARY KEY, so name/email are allowedSELECT c.id, c.name, c.email, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id;