// MySQL error reference

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
Error message
'%s' isn't in GROUP BY

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

  1. 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;
  2. 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;
  3. Group by the primary key, which lets you select the table's other columns.

Related errors

More syntax and statements errors

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.

// fewer errors, faster fixes

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.