GROUP BY query rejected by sql_mode only_full_group_by
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated columnShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
Error 1055 names a nonaggregated column such as mydb.t.addressA selected column is missing from GROUP BYFix - 2
You 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
Every row of a group carries identical values for the flagged columnYou know the values in each group are the sameFix - 4
Error 1140 appears: SELECT name, MAX(age) FROM tAn aggregate with no GROUP BY next to a plain columnFix
- 1
What does the error look like?
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated columnthis is incompatible with sql_mode=only_full_group_byERROR 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
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix