How to Fix only_full_group_by Error in MySQL

The only_full_group_by error occurs when MySQL requires all selected columns to be either aggregated or included in the GROUP BY clause. To fix it, either add all non-aggregated columns to GROUP BY or disable only_full_group_by mode in your SQL settings.

Why This Happens

This error happens because MySQL's only_full_group_by mode enforces strict rules on GROUP BY queries. It requires that every column in the SELECT list must be either inside an aggregate function like SUM() or COUNT(), or be included in the GROUP BY clause. If you select columns that are not aggregated or grouped, MySQL throws this error to avoid ambiguous results.

sql

SELECT id, name, COUNT(*) FROM users GROUP BY id;

Output

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

The Fix

To fix this error, you can either add all non-aggregated columns to the GROUP BY clause or use aggregate functions on them. Alternatively, you can disable the only_full_group_by mode, but this is less recommended because it can lead to ambiguous query results.

sql

SELECT id, name, COUNT(*) FROM users GROUP BY id, name;

Output

id | name  | COUNT(*)
1  | Alice | 3
2  | Bob   | 2

Prevention

Always include all non-aggregated columns in your GROUP BY clause to avoid this error. Use aggregate functions for columns you want summarized. You can check your current SQL modes with SELECT @@sql_mode; and avoid disabling only_full_group_by unless you understand the risks. Writing clear and explicit queries helps prevent ambiguous results and errors.

Related Errors

Other common grouping errors include:

• Incorrect GROUP BY usage: Forgetting to include necessary columns in GROUP BY.
• Ambiguous column errors: When columns are not clearly specified in joins or groupings.
• Aggregate function misuse: Using aggregates without proper grouping.

Fixes usually involve adjusting GROUP BY clauses or using aggregate functions correctly.

Key Takeaways

Include all non-aggregated columns in the GROUP BY clause to fix only_full_group_by errors.

Use aggregate functions like COUNT(), SUM(), or AVG() for columns you want summarized.

Avoid disabling only_full_group_by mode unless necessary to prevent ambiguous results.

Check your SQL mode with SELECT @@sql_mode; to understand current settings.

Write clear, explicit GROUP BY queries to prevent errors and ensure correct results.