🚨 MySQL Error Guide: 'isn't in GROUP BY' — Fix ERROR 1055 only_full_group_by

Overview

ERROR 1055 appears when only_full_group_by is enabled (the default since MySQL 5.7.5) and a query selects a column that is neither aggregated nor functionally dependent on the GROUP BY columns:

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

The error is MySQL protecting you from an ambiguous result: for each group there may be many customer_name values and the query does not say which one to return. Older MySQL silently picked one; the modern default refuses to guess.

Symptoms

• A GROUP BY query that ran on MySQL 5.6 or MariaDB with the old default fails after an upgrade to 5.7+/8.0.
• The message names a specific “Expression #N” and the offending nonaggregated column.
• Reports and dashboards that used loose grouping break, while strict, correct queries are unaffected.
• Reappears after a managed-service upgrade that resets sql_mode to the strict default.

Common Root Causes

• Selecting non-grouped, non-aggregated columns — e.g. SELECT customer_id, customer_name, SUM(total) ... GROUP BY customer_id where customer_name is neither grouped nor wrapped in an aggregate.
• Relying on the old “pick any value” behavior that MySQL used before 5.7.5.
• Grouping by a non-key column so MySQL cannot prove functional dependency (grouping by the primary key lets it prove other columns are determined).
• SELECT * with GROUP BY — pulls in many columns that are not functionally dependent on the grouping key.
• An upgrade or managed-service default change that enabled only_full_group_by.

Diagnostic Workflow

First, inspect the exact query and confirm the active SQL mode:

SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode;

Look for ONLY_FULL_GROUP_BY in the list. Identify the offending column named in “Expression #N,” then decide the correct semantics: do you want one representative value, an aggregate, or is the column actually functionally dependent on the grouping key?

If you group by the primary key, MySQL can prove dependency and the query is already valid:

-- Valid: grouping by the PK makes customer_name functionally dependent.
SELECT c.id, c.name, SUM(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

If the column genuinely can vary per group and you truly do not care which value, make that explicit with ANY_VALUE() so the intent is documented rather than accidental:

SELECT customer_id, ANY_VALUE(customer_name) AS name, SUM(total)
FROM orders
GROUP BY customer_id;

If the column should determine the group, add it to GROUP BY:

SELECT customer_id, customer_name, SUM(total)
FROM orders
GROUP BY customer_id, customer_name;

Example Root Cause Analysis

After a MySQL 8.0 upgrade, a nightly revenue report failed:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'app.orders.status' ...

SELECT @@GLOBAL.sql_mode confirmed ONLY_FULL_GROUP_BY was now on. The query was SELECT customer_id, status, SUM(total) FROM orders GROUP BY customer_id. Because a customer has orders in many status values, the old query had been returning an arbitrary status per customer for years — the numbers had always been subtly wrong. The correct fix was to add status to the grouping (GROUP BY customer_id, status), which both satisfied 1055 and produced the report the business actually needed. Disabling the SQL mode would have hidden a real correctness bug.

Prevention Best Practices

• Keep only_full_group_by enabled; it catches ambiguous queries that silently return wrong data.
• Group by the primary key when you want other columns of the same row, so MySQL proves functional dependency for you.
• Use ANY_VALUE() deliberately when an arbitrary representative is genuinely acceptable, so the intent is visible in the SQL.
• Avoid SELECT * with GROUP BY; list exactly the aggregated and grouping columns.
• Test queries against the production MySQL version before deploy, and treat 1055 after an upgrade as a correctness signal, not noise.

Quick Command Reference

SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;        -- is ONLY_FULL_GROUP_BY on?
-- Correct fixes (choose by intent):
GROUP BY id;                                         -- group by PK for dependency
SELECT ANY_VALUE(col) ...;                           -- explicit arbitrary value
GROUP BY customer_id, status;                        -- add the column to grouping

Conclusion

ERROR 1055 is not a bug to silence but a guard against ambiguous aggregation. Read the named “Expression #N,” decide whether you want an aggregate, an explicit ANY_VALUE(), or the column added to GROUP BY, and rewrite accordingly. Disabling only_full_group_by is possible but almost always masks a query that has been returning arbitrary, subtly wrong results — fix the query instead.