Fix: column “x” must appear in the GROUP BY clause or be used in an aggregate function

You ran a query with GROUP BY and PostgreSQL stopped with an error like this:

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

Why it happens

GROUP BY turns many rows into one row per group. Every column you select must have exactly one value per group. A grouping column has one by definition, and an aggregate such as COUNT or MAX produces one. Any other column could hold several different values, and PostgreSQL refuses to guess which one you mean.

SELECT c.city, c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.city;     -- London has Ada AND Chen: which name?

Fix 1: you wanted one row per thing

Often the grouping is simply too coarse. If you want one row per customer, group by the customer:

SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

PostgreSQL also accepts GROUP BY c.id alone here. When you group by a table’s primary key, it knows every other column of that table has a single value per group.

Fix 2: you wanted a summary of the column

If you really want one row per city, decide what to do with the names and say it with an aggregate:

SELECT c.city,
       COUNT(*)                   AS customers,
       MIN(c.name)                AS first_name_alphabetically,
       string_agg(c.name, ', ')   AS all_names     -- PostgreSQL
FROM customers AS c
GROUP BY c.city;

The list function is string_agg in PostgreSQL, GROUP_CONCAT in MySQL, and group_concat in SQLite (which also accepts string_agg since 3.44). If any value is fine, PostgreSQL 16+ and MySQL provide ANY_VALUE(c.name).

Fix 3: you wanted the whole row with the biggest value

A common goal is “the largest order per customer, with its other columns”. Selecting MAX(amount) plus other columns does not give you the matching row. Use a window function instead:

SELECT customer_id, id, amount
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders AS o
) AS ranked
WHERE rn = 1;

This works in PostgreSQL, MySQL 8.0+ and SQLite 3.25+. PostgreSQL also has SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, amount DESC.

Why it worked in SQLite or old MySQL

SQLite accepts the broken query and returns a value from an arbitrary row of each group. MySQL did the same before the ONLY_FULL_GROUP_BY mode became the default in 5.7.5; with it enabled, MySQL reports error 1055 instead. The query “worked” only by luck. Fix it as above rather than turning the mode off, so it gives the same answer on every database.