Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

GROUP BY query rejected by sql_mode only_full_group_by

ErrorERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Short answer

MySQL rejects the query because a selected column is neither in GROUP BY nor determined by it, so the value would be arbitrary. Fix the query: add the column to GROUP BY, group by a primary key, wrap it in ANY_VALUE(), or filter it to one value.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Confirm the mode is on and read the column name in the error. That column is the one to fix.

    SELECT @@SESSION.sql_mode;
  2. 2Match what you see, then jump to its fix

    1. 1Error 1055 names a nonaggregated column such as mydb.t.addressA selected column is missing from GROUP BYFix
    2. 2You group by name, but name is not the primary key or a unique NOT NULL columnGrouping by a column that is not a keyFix
    3. 3Every row of a group carries identical values for the flagged columnYou know the values in each group are the sameFix
    4. 4Error 1140 appears: SELECT name, MAX(age) FROM tAn aggregate with no GROUP BY next to a plain columnFix

What does the error look like?

  • ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
  • this is incompatible with sql_mode=only_full_group_by
  • ERROR 1140 (42000): In aggregated query without GROUP BY

Cause 1 of 4

A selected column is missing from GROUP BY

1Check

Decide whether each group really has one address, or whether you want one row per name and address.

SELECT name, COUNT(DISTINCT address) FROM t GROUP BY name;

2Fix

If you want one row per combination, add the column to GROUP BY.

SELECT name, address, MAX(age) FROM t GROUP BY name, address;

3Verify

The query runs. Check that the row count is what you expect, since it may grow.

SELECT COUNT(*) FROM (SELECT 1 FROM t GROUP BY name, address) x;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

Grouping by a column that is not a key

1Check

See which unique keys the table has.

SHOW INDEX FROM t;

2Fix

Group by the primary key. MySQL then knows every other column of that table is determined by it and accepts the query.

SELECT id, name, address, MAX(age) FROM t GROUP BY id;

3Verify

The query runs without error 1055.

SELECT @@SESSION.sql_mode;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

You know the values in each group are the same

1Check

Prove it with a count of distinct values per group; it must always be 1.

SELECT name FROM t GROUP BY name HAVING COUNT(DISTINCT address) > 1;

2Fix

If that returns no rows, tell MySQL any value will do with ANY_VALUE().

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

3Verify

The query returns one row per name.

SELECT COUNT(*), COUNT(DISTINCT name) FROM t;

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

An aggregate with no GROUP BY next to a plain column

1Check

Look for a plain column in the select list beside MAX, SUM or COUNT with no GROUP BY.

SELECT name, MAX(age) FROM t;

2Fix

Group by the plain column, or restrict it to a single value with WHERE.

SELECT name, MAX(age) FROM t GROUP BY name;

3Verify

The query returns without error.

SELECT @@SESSION.sql_mode;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Write GROUP BY queries so every plain column is grouped, keyed or wrapped in an aggregate.
  • Do not switch off ONLY_FULL_GROUP_BY to silence the error; the server then picks any value per group, so results become unpredictable.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.