How do I fix MySQL error 1055 "only_full_group_by"?

A query that has worked for years fails after upgrading MySQL, or moving to a new host:

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

The first Stack Overflow answer says to remove ONLY_FULL_GROUP_BY from sql_mode. Don't. The error is pointing at a real bug in the query.

What the query was doing

SELECT o.customer_id, o.created_at, SUM(o.total) AS spent
FROM orders o
GROUP BY o.customer_id;

Each customer has many orders, and so many created_at values. Which one should the result show? The SQL standard says the query is invalid. Old MySQL (before 5.7) quietly picked an arbitrary row's value. That's usually the first one it read, but it's not guaranteed and can change with indexes, versions or data. The results looked right until they didn't.

Since MySQL 5.7.5, ONLY_FULL_GROUP_BY is on by default and rejects these queries. PostgreSQL, SQL Server and Oracle have always rejected them.

Fix it by saying what you mean

You want an aggregate (first order, last order):

SELECT customer_id, MIN(created_at) AS first_order, MAX(created_at) AS last_order,
       SUM(total) AS spent
FROM orders
GROUP BY customer_id;

The column is the same for every row in the group anyway. MySQL recognizes functional dependency on a primary key, so grouping by a table's PK allows its other columns:

SELECT c.id, c.name, c.email, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;          -- OK: name and email depend on c.id (the PK)

You really don't care which value (for example a sample value for display):

SELECT customer_id, ANY_VALUE(created_at) AS some_order_date, SUM(total)
FROM orders
GROUP BY customer_id;

ANY_VALUE() documents the intent and keeps the query legal. Use it sparingly.

You want the whole latest row per group. This is the most common real intent ("each customer's most recent order"), and GROUP BY is the wrong tool. Use a window function (MySQL 8.0+):

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

On 5.7, join back to a grouped subquery of MAX(created_at) per customer, and add a tie-breaker on id.

DISTINCT and ORDER BY trigger it too

SELECT DISTINCT customer_id FROM orders ORDER BY created_at;   -- also rejected

Sorting distinct customers by one of many created_at values is ambiguous in the same way. Order by an aggregate in a grouped query instead: GROUP BY customer_id ORDER BY MAX(created_at).

Why not just turn it off?

SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));  -- please don't

• The queries go back to returning nondeterministic values. Reports can silently change after an index or upgrade.
• You lose portability to every other SQL database.
• It's a server-wide setting that also affects code that was correct.

If you inherited hundreds of such queries, a temporary per-session relaxation during a migration can buy time. Fix them one by one, starting with anything that feeds money, reports or decisions.

Checklist

1. Error 1055 means a selected column isn't grouped, aggregated or functionally dependent on the group.
2. Aggregate it (MIN/MAX/…), group by the primary key, or use ANY_VALUE() deliberately.
3. For "latest row per group", use ROW_NUMBER() rather than GROUP BY.
4. Keep ONLY_FULL_GROUP_BY on. It catches real bugs.