MySQL raises error 3066 when the ORDER BY clause contains an aggregate expression that is missing from the SELECT list while ONLY_FULL_GROUP_BY or a similar SQL mode is enabled.
ER_AGGREGATE_IN_ORDER_NOT_SELECT occurs when an ORDER BY uses an aggregate not present in the SELECT list. Add the same aggregate to SELECT, use a subquery, or disable ONLY_FULL_GROUP_BY to resolve the issue.
ER_AGGREGATE_IN_ORDER_NOT_SELECT
MySQL throws error 3066 when the ORDER BY clause contains an aggregate function that is not also present in the SELECT list and ONLY_FULL_GROUP_BY or a related SQL mode is active.
The rule, added in MySQL 5.7.5, enforces deterministic ordering of grouped results. The optimizer halts execution and returns the error before any data is read or written.
The error appears in GROUP BY or DISTINCT queries that try to sort by SUM(), COUNT(), MAX(), AVG(), or similar functions without selecting that same expression. It can also surface in UNION queries with aggregated ORDER BY.
Teams upgrading from MySQL 5.6 or enabling strict SQL modes often encounter the error in legacy reports and stored procedures.
Unresolved, the error blocks reports, dashboards, API endpoints, and ETL jobs that rely on the query. Fixing the query restores functionality and prevents production incidents.
The main trigger is the ONLY_FULL_GROUP_BY mode, which disallows nonselected aggregates in ORDER BY. The mode is enabled by default in modern MySQL versions.
Another cause is mixing ROLLUP or DISTINCT with aggregated ORDER BY expressions that are not part of the result set.
Add the aggregated expression to the SELECT list so the ORDER BY no longer references an unseen column.
Alternatively, wrap the original query in a subquery and perform ORDER BY in the outer query, or disable ONLY_FULL_GROUP_BY if data ambiguity is not a concern.
Reporting queries that group sales by day but order by SUM(amount) fail after upgrades. Adding SUM(amount) AS total to the SELECT list resolves the issue.
Analytics tools that auto-generate ORDER BY COUNT(*) may break; adjusting the tool template or using a subquery prevents the error.
Always include any aggregated column used in ORDER BY within the SELECT list. Use clear column aliases to simplify maintenance.
Enable query linting in Galaxy or CI pipelines to catch the pattern before deployment. Write integration tests for critical reports.
Error 1055 (ONLY_FULL_GROUP_BY) occurs when nonaggregated columns in SELECT are not in GROUP BY. The fix is to aggregate or group the offending column.
Error 3065 (ORDER_BY_IN_SELECT_LIST) arises when ORDER BY references a column not in SELECT without aggregation. Add the column or use DISTINCT.
ORDER BY uses SUM(), COUNT(), or another aggregate that the SELECT list omits.
The SQL mode enforces strict grouping rules starting in MySQL 5.7.5.
Auto-generated queries often place aggregates in ORDER BY without adding them to SELECT.
Queries using GROUP BY ... WITH ROLLUP can trigger the error when ordering by an aggregate.
Nonaggregated columns in SELECT are not included in GROUP BY.
ORDER BY references a nonselected column without aggregation.
Mixing aggregated and nonaggregated columns in SELECT without GROUP BY.
MySQL 5.7 enables ONLY_FULL_GROUP_BY by default, which triggers error 3066 when ORDER BY aggregates are missing from SELECT.
You can, but doing so risks nondeterministic results. It is safer to rewrite the query.
It adds an extra column. You can alias it or wrap the query so external consumers see the same fields.
Galaxy lints queries in real time and the AI copilot proposes compliant rewrites before execution, reducing production errors.
.avif)
.avif){kind=logo}
