Column must appear in the GROUP BY clause: what SQL is asking

You grouped your rows and selected a column, and the database refused. PostgreSQL says the column “must appear in the GROUP BY clause or be used in an aggregate function”, and DuckDB, which runs the queries on this page, says the same thing in nearly the same words. It is not being fussy. It is asking you a question your query did not answer.

What the error means

GROUP BY customer folds every row for one customer into a single row. Any other column you select has to say what to do with the many values it had. Here, each customer has several orders, so amount has several values and SQL cannot pick one for you:

WITH orders(customer, amount) AS (
  VALUES ('Ava', 120), ('Ava', 80), ('Liam', 300)
)
SELECT customer, amount
FROM orders
GROUP BY customer;

Binder Error: column "amount" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use "ANY_VALUE(amount)" if the exact value of "amount" is not important.

LINE 4: SELECT customer, amount
                         ^

Fix 1: aggregate the column

Most of the time you wanted a total, an average or a count. Say which, and the question is answered:

WITH orders(customer, amount) AS (
  VALUES ('Ava', 120), ('Ava', 80), ('Liam', 300)
)
SELECT customer, SUM(amount) AS total, COUNT(*) AS orders
FROM orders
GROUP BY customer
ORDER BY customer;

Fix 2: group by it too

If the column really does belong in each group, add it to GROUP BY. Be careful: this makes the groups smaller. Ava’s two orders are now two separate rows, because they have different amounts:

WITH orders(customer, amount) AS (
  VALUES ('Ava', 120), ('Ava', 80), ('Liam', 300)
)
SELECT customer, amount
FROM orders
GROUP BY customer, amount
ORDER BY customer, amount;

That is the fix that quietly changes your answer, so only use it when you meant to group by both.

Fix 3: say any value will do

When every row in a group has the same value, such as a customer’s email next to their id, you can tell the database to take any one. DuckDB and PostgreSQL 16 call this ANY_VALUE. Before MySQL 5.7.5 its default settings took a value from the group without being asked, which is why queries like this worked there and fail elsewhere. ANY_VALUE makes that choice something you wrote down.

Why MySQL let you get away with it

Before MySQL 5.7.5 the default settings accepted the original query. For each customer it returned an amount taken from one of that customer’s rows, and which one was up to the server: it was free to pick any of them, with no promise about which. The result looked right and was not. MySQL 5.7.5 made ONLY_FULL_GROUP_BY part of the default mode, so a current install refuses the query with an error of its own, unless that mode has been switched off. A habit formed on the old behaviour fails the day you move to a database that never allowed it.