This error occurs when a GROUP BY query selects columns that are not aggregated or included in the GROUP BY clause. Add missing columns to GROUP BY or wrap them in aggregate functions like MAX(), MIN(), or ANY_VALUE().
Fixes ERROR 1056: Can't group on column
SELECT name, address, MAX(age)
FROM users
GROUP BY name;SELECT name, address, MAX(age)FROM usersGROUP BY name;ERROR 1056: Can't group on column
On this page
ERROR 1056 (ER_WRONG_GROUP_FIELD) appears when MySQL's strict GROUP BY mode (ONLY_FULL_GROUP_BY) is enabled, which is the default in MySQL 5.7+. The error occurs because your SELECT statement includes columns that are not part of the GROUP BY clause and are not wrapped in an aggregate function. MySQL cannot determine which value to return for each group, so it halts execution. This enforces standard SQL behavior and prevents silent data inconsistencies that can occur when columns have multiple possible values per group.
The most direct solution is to include every column in the GROUP BY that appears in the SELECT clause without an aggregate function.
Before (causes ERROR 1056):
SELECT name, address, MAX(age)
FROM users
GROUP BY name;After (correct):
SELECT name, address, MAX(age)
FROM users
GROUP BY name, address;This ensures MySQL knows exactly which value to return for each column in each group.
If you don't need all columns in the GROUP BY, wrap them in an aggregate function. Choose the appropriate function based on what value you want:
-- Get first address value
SELECT name, MIN(address), MAX(age)
FROM users
GROUP BY name;
-- Or use ANY_VALUE() when any value is acceptable
SELECT name, ANY_VALUE(address), MAX(age)
FROM users
GROUP BY name;The ANY_VALUE() function explicitly tells MySQL to pick an arbitrary value, which is useful when you only care about the grouped column.
If a selected column is functionally dependent on a GROUP BY column (like when GROUP BY uses a primary key or unique NOT NULL column), MySQL will allow it without aggregation:
-- If user_id is a primary key, address is allowed without aggregation
SELECT user_id, address, MAX(age)
FROM users
GROUP BY user_id;This works because each user_id uniquely identifies one row, so address has only one possible value per group. Check your schema to identify such columns.
For MySQL 8.0+, window functions provide an alternative to GROUP BY that doesn't require all columns to be aggregated:
-- Get each user with their age and max age in their group
SELECT
name,
address,
age,
MAX(age) OVER (PARTITION BY department) as max_dept_age
FROM users;This approach avoids GROUP BY entirely while still performing grouped calculations.
When to disable ONLY_FULL_GROUP_BY:
While disabling ONLY_FULL_GROUP_BY with SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); is possible, this is a last resort. Disabling strict mode risks non-deterministic query results that vary unpredictably based on storage order or data changes.
Functional dependence explained:
MySQL recognizes that a column is "functionally dependent" on a GROUP BY column when the GROUP BY column uniquely identifies each row. For example, if you GROUP BY employee_id (a primary key), MySQL knows each group has only one employee_name value, so you can SELECT it without aggregation.
Performance considerations:
Adding more columns to GROUP BY increases memory and CPU usage. If you have many optional columns, consider using ANY_VALUE() instead of adding them to GROUP BY to keep the grouping operation focused on key columns.