EC
2024
MySQLERRORNotableQueryHIGH confidence

Mixing aggregate and non-aggregate columns without GROUP BY

Production Risk

Low — query is rejected; no data is modified.

What this means

A SELECT statement mixes aggregate functions (COUNT, SUM, MAX, etc.) with non-aggregated columns, but does not include a GROUP BY clause. With ONLY_FULL_GROUP_BY SQL mode enabled, this is an error.

Why it happens
  1. 1SELECT includes both aggregate functions and bare column references without GROUP BY.
  2. 2ONLY_FULL_GROUP_BY SQL mode is enabled (default in MySQL 5.7+).
  3. 3Non-deterministic column selection in aggregation context.
How to reproduce
trigger — this will error
trigger — this will error
SELECT name, COUNT(*) FROM orders; -- name is not in GROUP BY

expected output

ERROR 2024 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'orders.name'; this is incompatible with sql_mode=only_full_group_by.

Fix 1

Add a GROUP BY clause for the non-aggregated columns

Add a GROUP BY clause for the non-aggregated columns
SELECT name, COUNT(*) FROM orders GROUP BY name;

Why this works

GROUP BY defines the grouping key, making non-aggregated columns deterministic.

Fix 2

Wrap the column in an aggregate function if that is the intent

Wrap the column in an aggregate function if that is the intent
SELECT MAX(name), COUNT(*) FROM orders;

Why this works

Aggregating all columns satisfies the ONLY_FULL_GROUP_BY requirement.

What not to do

✕

Version notes

Sources
Official documentation ↗

MySQL 8.0 — 2024 ER_MIX_OF_GROUP_FUNC_AND_FIELDS

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors