The query selects nonaggregated columns alongside aggregate functions without a GROUP BY clause while ONLY_FULL_GROUP_BY mode is active.
ER_MIX_OF_GROUP_FUNC_AND_FIELDS_V2 appears in MySQL when a statement mixes plain columns with aggregate functions but omits a GROUP BY while sql_mode has ONLY_FULL_GROUP_BY enabled. Add a matching GROUP BY or wrap the column in an aggregate to fix it.
ER_MIX_OF_GROUP_FUNC_AND_FIELDS_V2
MySQL raises error code 3088 when a SELECT statement includes aggregate functions such as COUNT or SUM and simultaneously returns at least one column that is not aggregated, yet the query omits a GROUP BY clause. The server blocks the statement because ONLY_FULL_GROUP_BY mode demands deterministic grouping.
The error was introduced in MySQL 5.7.6 to tighten SQL compliance and prevent ambiguous results. It surfaces most often after upgrades when ONLY_FULL_GROUP_BY becomes part of the default sql_mode.
The root cause is a mismatch between selected columns and grouping rules. A column that is neither part of an aggregate function nor listed in GROUP BY violates ONLY_FULL_GROUP_BY.
Another trigger is referencing expressions based on nonaggregated columns, such as ORDER BY col_name, when col_name is not grouped. Derived tables or views created before the mode change can also fail when executed under stricter settings.
The primary remedy is to rewrite the query so every selected column is either aggregated or appears in a GROUP BY clause that matches the functional dependency rules.
Alternatively, remove ONLY_FULL_GROUP_BY from sql_mode, but this is discouraged in production because it re-introduces nondeterministic results.
Developers frequently hit the error while counting rows and also selecting an ungrouped descriptive column. Adding that column to GROUP BY or moving the aggregate into a subquery resolves the issue.
Reporting queries that sort by a column not included in GROUP BY also fail. Wrapping the sort column in an aggregate like MAX or listing it in GROUP BY clears the error.
Always design SELECT statements so that the GROUP BY clause lists every nonaggregated column. Adopt a query linter or IDE like Galaxy that highlights grouping violations before execution.
Enable automated tests that run under ONLY_FULL_GROUP_BY in staging to catch legacy queries early. Refactor old views by explicitly aggregating or grouping all selected fields.
ER_MIX_OF_GROUP_FUNC_AND_FIELDS (code 1140) is the pre-5.7 equivalent with identical causes and fixes. ER_WRONG_FIELD_WITH_GROUP also involves illegal field selection in grouped queries. Address them with the same aggregation and grouping strategies.
Selecting a nonaggregated column alongside COUNT, SUM, AVG, etc., without a GROUP BY clause.
Referencing an expression built on a nonaggregated column in ORDER BY or HAVING while ONLY_FULL_GROUP_BY is active.
Running legacy views or stored procedures created before MySQL 5.7 under stricter sql_mode settings.
Accidentally enabling ONLY_FULL_GROUP_BY during an upgrade without refactoring existing reporting queries.
The older variant of the same rule raised before MySQL 5.7.
Appears when a selected field is not in GROUP BY and not functionally dependent on it.
Triggered in stored functions that reference non-grouped fields.
Yes, it allows nondeterministic results where MySQL picks an arbitrary row for ungrouped columns.
It was added in 5.7.6 and remains active in all newer versions including 8.0.
Using ANY_VALUE(column) explicitly signals that the picked value is irrelevant, which bypasses the rule safely.
Galaxy flags nonaggregated columns in queries and suggests GROUP BY clauses via its AI copilot, preventing the error before execution.
.avif)
.avif){kind=logo}
