MySQL GROUP BY Clause Failing With ONLY_FULL_GROUP_BY Enabled

ONLY_FULL_GROUP_BY, enabled by default since MySQL 5.7, enforces the SQL standard requirement that any column selected without being wrapped in an aggregate function must also appear in the GROUP BY clause — queries that worked fine on older MySQL versions with more lenient default behavior can suddenly fail after an upgrade, even though the query's actual intent may have been perfectly reasonable.

The Problem

A query with a GROUP BY clause that used to work fails after a MySQL upgrade, or on a server with standard modern defaults:

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

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

Why It Happens

Before ONLY_FULL_GROUP_BY was enabled by default, MySQL allowed selecting a non-aggregated, non-grouped column and would simply pick an arbitrary value for it from within each group — technically permitted by MySQL's older, more lenient behavior, but genuinely ambiguous and non-standard, since there's no well-defined rule for which specific row's value gets picked when multiple rows share the same group. ONLY_FULL_GROUP_BY enforces the stricter, standard SQL requirement: every selected column must either be part of the GROUP BY clause itself, or wrapped in an aggregate function (SUM, MAX, COUNT, etc.), removing this ambiguity entirely by requiring the query to be explicit about how each non-grouped column's value should actually be determined.

The Fix

The most correct fix, in the vast majority of cases, is deciding explicitly what should happen with the problematic column, rather than simply disabling the mode to restore the old, ambiguous behavior. If you want a specific aggregate value (like the most recent order date per customer), wrap the column in an appropriate aggregate function:

SELECT customer_id, MAX(order_date) AS latest_order_date, SUM(total)
FROM orders
GROUP BY customer_id;

This is explicit and unambiguous — MAX(order_date) clearly states you want the most recent date within each customer's group, rather than leaving it to an arbitrary, undefined selection as the old lenient behavior effectively did.

If the column should genuinely be part of the grouping itself (meaning you actually want a separate row per distinct combination of customer and date, not one row per customer with an arbitrarily chosen date), add it to the GROUP BY clause:

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

This produces a genuinely different result set than the aggregate-function approach above — one row per unique customer/date combination, rather than one row per customer overall — so the correct choice depends entirely on what your query is actually meant to answer, which is worth thinking through carefully rather than picking whichever fix happens to make the error disappear fastest.

If the non-grouped column is functionally dependent on the grouped column (for example, grouping by a primary key, where every other column in that same table is inherently unique per group by definition), MySQL's ONLY_FULL_GROUP_BY is actually smart enough to recognize certain genuine functional dependencies and allow them without requiring the column to also be explicitly listed — if you're confident this genuinely applies to your specific case but still see the error, double check you're actually grouping by a true primary or unique key, not just a column that happens to often have unique values in your current dataset without a genuine constraint enforcing it.

As a last resort, and generally not recommended for anything beyond very targeted, well-understood legacy compatibility needs, you can disable the mode — but be aware this reintroduces the exact ambiguity the mode exists to prevent, and any query relying on this disabled behavior effectively has undefined, unpredictable results for the non-grouped column:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Treat this as a temporary compatibility bridge while you fix the underlying queries properly, not a permanent solution — every query relying on this disabled behavior should eventually be rewritten with explicit aggregate functions or expanded GROUP BY clauses, since the ambiguous result is fundamentally fragile and could silently change between MySQL versions or even between separate executions of the same query.

Still Not Working?

If you have many queries across a large, established codebase that all need this fix after an upgrade, and rewriting every single one immediately isn't practical, use the temporary sql_mode disabling as a genuinely temporary bridge specifically to avoid blocking the upgrade, while tracking and incrementally fixing each affected query's specific column handling over time — treating this as technical debt with a clear remediation plan, rather than a permanent configuration choice, keeps the codebase moving toward the more robust, standard-compliant behavior without requiring the upgrade itself to be blocked on every single query being immediately rewritten all at once.