MySQL error 1055: Expression isn't in GROUP BY
With ONLY_FULL_GROUP_BY on (the default since MySQL 5.7), every selected column must be grouped, aggregated, or functionally dependent on the grouped columns.
- Error number
1055- Name
ER_WRONG_FIELD_WITH_GROUP- SQLSTATE
42000- Category
- Syntax and statements
What it means
In a grouped query each output row stands for a group of input rows. A selected column that is neither in GROUP BY nor inside an aggregate has many possible values per group. Older MySQL picked one arbitrarily; with ONLY_FULL_GROUP_BY, which is on by default since 5.7, it's an error unless MySQL can prove the column is determined by the grouped columns (for example, grouping by a primary key).
Common causes
- Queries written for MySQL 5.6 or earlier that relied on the loose behavior.
- Grouping by one column and selecting other non-aggregated columns.
- Ordering by a column that isn't grouped.
How to fix it
-
Add the column to
GROUP BY, or aggregate it.SELECT customer_id, MAX(created_at) AS last_order, COUNT(*) AS n FROM orders GROUP BY customer_id;
-
If any value from the group is acceptable, say so with
ANY_VALUE().SELECT customer_id, ANY_VALUE(status) AS some_status FROM orders GROUP BY customer_id;
-
Group by the primary key, which lets you select the table's other columns.
Related errors
8120SQL Server: Column not in GROUP BY or aggregate42803PostgreSQL: column must appear in the GROUP BY clauseORA-00979Oracle: not a GROUP BY expression
More syntax and statements errors
1064You have an error in your SQL syntax1149SQL syntax error1052Column is ambiguous1093Can't specify target table for update1136Column count doesn't match value count1242Subquery returns more than 1 row1248Every derived table must have its own alias1318Incorrect number of arguments
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.