Expression not in GROUP BY and not in aggregate — ONLY_FULL_GROUP_BY
Production Risk
Low — query is rejected cleanly; fix the SQL.
ER_WRONG_GROUP_FIELD (1055, SQLSTATE 42000) is raised when ONLY_FULL_GROUP_BY SQL mode is active and a SELECT list contains a non-aggregated column that is not in the GROUP BY clause. MySQL 5.7+ enables this mode by default.
- 1SELECT includes a column that is neither in GROUP BY nor wrapped in an aggregate function
- 2Query was written for MySQL 5.6 (where ONLY_FULL_GROUP_BY was off by default) and then run on 5.7+
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id; -- 'name' is not in GROUP BY and not aggregated
expected output
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.orders.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Fix
Add the column to GROUP BY or wrap it in ANY_VALUE()
WHEN The column is functionally dependent on the GROUP BY key.
-- Option 1: Add to GROUP BY SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id, name; -- Option 2: Use ANY_VALUE() if you don't care which value is picked SELECT user_id, ANY_VALUE(name), COUNT(*) FROM orders GROUP BY user_id;
Why this works
ANY_VALUE() tells MySQL to pick any value of the column within the group, suppressing the ONLY_FULL_GROUP_BY check.
✕ Disable ONLY_FULL_GROUP_BY globally to suppress the error
ONLY_FULL_GROUP_BY catches ambiguous queries that return non-deterministic results; disabling it hides real bugs.
ONLY_FULL_GROUP_BY was added to the default sql_mode, making 1055 much more common on upgrade from 5.6.
MySQL 8.0 — 1055 ER_WRONG_GROUP_FIELD
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev