Column must appear in the GROUP BY clause: the PostgreSQL fix

column "amount" must appear in the GROUP BY clause or be used in an aggregate function means a column in your SELECT list is neither listed in GROUP BY nor wrapped in an aggregate such as SUM, MAX or COUNT. Each group becomes one row, so the database cannot pick one of the many values that column holds. The usual fix is to aggregate the column, for example SUM(amount), or to add it to GROUP BY.

What does “must appear in the GROUP BY clause” mean?

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 several values it had. Here, Ava has two orders, so amount has two values for her and SQL refuses to choose:

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
                         ^

The SQL engine on this page words the message slightly differently and adds a hint. PostgreSQL puts the table name in front of the column:

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

Rails shows the same message after PG::GroupingError, and Python code that goes through psycopg2, SQLAlchemy included, shows it after psycopg2.errors.GroupingError. The fixes are the same in every case.

How do I fix it with SUM, MAX or COUNT?

An aggregate such as SUM, MAX or COUNT is the right fix most of the time, because you usually wanted a total, a largest value or a count per group. Name the one you meant, and every group has a single answer:

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

Should I just add the column to GROUP BY?

Adding the column to GROUP BY is right only when you really want one row per combination of both columns. It makes the groups smaller: Ava’s two orders become two 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;

Adding a column to GROUP BY silences the error and quietly changes your answer, so do it only when you meant to group by both.

When is ANY_VALUE the right fix?

ANY_VALUE is the right fix when every row in a group holds the same value, such as a customer’s email next to their id. It tells the database to take any one of them. PostgreSQL 16 and later, MySQL 5.7 and later, and the engine on this page all have it:

WITH orders(customer_id, email, amount) AS (
  VALUES (1, 'ava@example.com', 120), (1, 'ava@example.com', 80), (2, 'liam@example.com', 300)
)
SELECT customer_id, ANY_VALUE(email) AS email, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Never use ANY_VALUE on a column whose values differ inside a group. You get one of them, with no promise about which, and the result looks right when it is not.

When you group by a table’s primary key, PostgreSQL and MySQL let you select that table’s other columns with no aggregate at all, because each key has exactly one value for them. The manuals call this functional dependence. That is why GROUP BY customers.id with customers.email works on a table whose key is id, and the same query fails on a table without a primary key, or when you group by a column that is not the key.

How do I get the whole row with the highest value in each group?

ROW_NUMBER() OVER (PARTITION BY ...) is the portable way to get the whole row with the highest value in each group, such as each customer’s largest order with its date. MAX(amount) alone cannot do it: it gives the largest amount, and asking for order_date next to it brings the GROUP BY error straight back. Number the rows inside each group instead, largest first, and keep row 1:

WITH orders(customer, order_date, amount) AS (
  VALUES ('Ava', '2026-10-01', 120), ('Ava', '2026-10-03', 80),
         ('Liam', '2026-10-02', 300), ('Liam', '2026-10-05', 150)
),
ranked AS (
  SELECT customer, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer, order_date, amount
FROM ranked
WHERE rn = 1
ORDER BY customer;

The filter on rn sits in the outer query because a window function is not allowed in the WHERE of the query that computes it. With orders as a real table in place of the VALUES list, the ranked query and the filter around it run unchanged on PostgreSQL, MySQL 8, SQL Server, Oracle and SQLite. On a tie, ROW_NUMBER keeps one row; use RANK() instead to keep every tied row. PostgreSQL also has a shorter form of its own, SELECT DISTINCT ON (customer) ... ORDER BY customer, amount DESC, which other databases do not accept.

Why do ORDER BY and HAVING give the same error?

ORDER BY and HAVING run after the rows are grouped, so they follow the same rule as SELECT: a column there must be grouped or aggregated too. Sorting by amount after grouping by customer fails for the same reason as selecting it:

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

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 7: ORDER BY amount;
                 ^

Sort by the aggregate instead, ORDER BY total or ORDER BY SUM(amount), and write HAVING SUM(amount) > 100 rather than HAVING amount > 100.

Why does the query work in MySQL but fail in PostgreSQL?

MySQL before 5.7.5 accepted the first query on this page by default. For each customer it returned an amount from one of that customer’s rows, and the server was free to pick any of them. MySQL 5.7.5 made ONLY_FULL_GROUP_BY part of the default SQL mode, so a current install refuses the query too, unless the mode has been switched off or the grouped column is the table’s primary key. PostgreSQL has never allowed it.

MySQL 8.4 prints a different message for the same mistake. For a table orders in a database shop, it reads:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Turning ONLY_FULL_GROUP_BY off makes the message go away and brings back the guessed values. Fix the query with one of the fixes on this page instead; ANY_VALUE is the one that keeps MySQL’s old behaviour, written down.

What do SQL Server and Oracle call the same error?

SQL Server and Oracle refuse the same query with their own wording. SQL Server reports error 8120, and Oracle 19c reports ORA-00979:

Column 'orders.amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ORA-00979: not a GROUP BY expression

The cause and the fixes are the same as in PostgreSQL. The PostgreSQL GROUP BY reference states the rule and the primary-key exception, the aggregate functions page documents any_value, the window functions tutorial covers ROW_NUMBER, and the MySQL manual on GROUP BY handling explains ONLY_FULL_GROUP_BY.