How to Fix MySQL 'Expression #1 of SELECT list is not in GROUP BY clause'

In this tutorial, you'll learn about How to Fix MySQL 'Expression #1 of SELECT list is not in GROUP BY clause. We cover key concepts, practical examples, and best practices.

The Problem

Running a SELECT query with GROUP BY fails with Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column because MySQL's ONLY_FULL_GROUP_BY SQL mode is enabled.

Quick Fix

Add All Non-Aggregated Columns to GROUP BY

mysql -u root -e "
SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
"
# department   emp_count   avg_salary
# Engineering  15          95000.00
# Sales        10          62000.00

The fix is to list every non-aggregated column (columns not wrapped in COUNT(), SUM(), AVG(), etc.) in the GROUP BY clause.

Use ANY_VALUE() to Suppress the Error

mysql -u root -e "
SELECT department, ANY_VALUE(location) as loc, COUNT(*) as cnt
FROM employees
GROUP BY department;
"
# department   loc         cnt
# Engineering  Building A  15
# Sales        Building B  10

ANY_VALUE() picks an arbitrary value from the group for columns not in GROUP BY. Use this when you know the value is functionally dependent on the grouped column.

Check the Current SQL Mode

mysql -u root -e "SELECT @@sql_mode;"
# @@sql_mode
# ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,...

ONLY_FULL_GROUP_BY is enabled by default in MySQL 5.7+. Check whether it is active before debugging query issues.

Disable ONLY_FULL_GROUP_BY Temporarily

mysql -u root -e "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));"
# Query OK, 0 rows affected
mysql -u root -e "SELECT @@sql_mode;"
# @@sql_mode
# STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,...

Remove ONLY_FULL_GROUP_BY from the global SQL mode as a temporary workaround. Re-enable it after fixing your queries.

Set ONLY_FULL_GROUP_BY Per Session

mysql -u root -e "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
mysql -u root -e "SELECT department, location, COUNT(*) FROM employees GROUP BY department;"

Disable ONLY_FULL_GROUP_BY for the current session only to test a query. This avoids changing the global setting. Once you identify the fix, update the query permanently rather than relying on the relaxed mode.

Additional Troubleshooting

# Check the error message and stack trace for more context
echo "Review the full error output to identify the root cause"

If the above steps do not resolve the issue, examine the complete error message and stack trace. Often the key detail is in the middle of the traceback rather than the final line. Search for the error message in the project documentation or issue tracker for additional solutions.

Use ANY_VALUE with Caution

mysql -u root -e "
SELECT department, ANY_VALUE(employee_name), COUNT(*)
FROM employees
GROUP BY department;
"

ANY_VALUE returns a value from an unspecified row in each group. Use it only when you are certain the value is the same for all rows in the group or when any value is acceptable.

Prevention

• Always list every non-aggregated column in GROUP BY to write portable SQL
• Use ANY_VALUE() sparingly and only when the value is functionally dependent on the GROUP BY column
• Keep ONLY_FULL_GROUP_BY enabled in development to catch problematic queries early
• Use GROUP BY with a primary key in the select list to satisfy the rule unambiguously