Fix "column must appear in the GROUP BY clause" in PostgreSQL
ERROR: column "customers.active_status" must appear in the GROUP BY clause or be used in an aggregate function
GROUP BY squashes many rows into one row per group. Postgres is asking: this column has several different values inside each group, so which one should it show? Either group by that column too, or tell Postgres how to combine its values.
Why it happens
- You added a column to SELECT but forgot to add it to GROUP BY.
- You want one detail per group (like the latest date) but didn't say how to pick it, for example with MAX().
- You want every row plus a group total next to it, which is a window function job, not a GROUP BY job.
- You come from MySQL, which quietly picks a value for ungrouped columns. Postgres refuses to guess.
How to fix it
1. Add the column to GROUP BY
Fails
SELECT country, active_status, COUNT(*) AS customers FROM customers GROUP BY country;
Works
SELECT country, active_status, COUNT(*) AS customers FROM customers GROUP BY country, active_status ORDER BY country;
If you want one row per country and per status, both columns belong in GROUP BY. Every plain column in SELECT must be listed there.
2. Wrap the column in an aggregate
Fails
SELECT customer_id, order_date, SUM(amount) AS total_spent FROM orders GROUP BY customer_id;
Works
SELECT customer_id, MAX(order_date) AS last_order, SUM(amount) AS total_spent FROM orders GROUP BY customer_id;
Each customer has several order dates. MAX() picks the latest, MIN() the first. Grouping by order_date instead would give one row per customer per day, which is usually not what you want.
3. Keep every row with a window function
Fails
SELECT id, customer_id, amount, SUM(amount) AS customer_total FROM orders GROUP BY customer_id;
Works
SELECT id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total FROM orders ORDER BY customer_id, id;
OVER (PARTITION BY ...) adds the group total to every row without collapsing them. Tip: grouping by a table's primary key (GROUP BY c.id) lets you select any other column of that table, like c.name.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Revenue per customerCOUNT · SUM · GROUP BY
- Orders by customer and statusGROUP BY two columns
- Revenue by countryJOIN + GROUP BY
- Paid above department averageAVG() OVER · compare to group