How to Fix MySQL Error 1055: Expression Not in GROUP BY (ONLY_FULL_GROUP

ERROR 1055 (42000): Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table.column' which is not functionally dependent on columns in GROUP BY clause is raised when a SELECT query references a column that is neither listed in the GROUP BY clause nor wrapped in an aggregate function, while the ONLY_FULL_GROUP_BY SQL mode is active. The error symbol is ER_WRONG_FIELD_WITH_GROUP.

Impact

The query is rejected immediately and no rows are returned. This is a strict correctness check: without it, MySQL would silently return an arbitrary value for the ungrouped column (whichever row happened to be selected internally), making the result non-deterministic. The error became the default behavior in MySQL 5.7, where ONLY_FULL_GROUP_BY was added to the default SQL mode set, so applications that ran without issues on MySQL 5.6 or earlier may start failing after an upgrade.

The error surfaces in application logs as a database exception, often wrapped by an ORM such as Hibernate, ActiveRecord, SQLAlchemy, or Django ORM with a message like OperationalError, QueryException, or similar — but the underlying MySQL error code is always 1055.

Common Causes

Selecting a non-aggregated column that is not in GROUP BY. The most frequent cause: a column appears in SELECT but is absent from both the GROUP BY list and any aggregate call (COUNT, SUM, MAX, etc.).
Upgrading from MySQL 5.6 (or earlier) to 5.7+. MySQL 5.6 and earlier did not include ONLY_FULL_GROUP_BY in the default SQL mode, so queries that silently returned non-deterministic results now fail explicitly.
ORM-generated queries. Some ORM versions or plugins generate GROUP BY clauses that omit selected columns, especially when using custom select() calls or raw SQL fragments alongside ORM grouping helpers.
Using GROUP BY with aliases. Referencing a SELECT alias in GROUP BY works in some dialects but MySQL requires the original expression or column name; a mismatch can trigger this error.
DISTINCT replaced manually with GROUP BY. Developers sometimes rewrite SELECT DISTINCT as GROUP BY but forget to include all selected columns in the group list.

Troubleshooting and Resolution Steps

Identify the current SQL mode to confirm ONLY_FULL_GROUP_BY is active.
```
SELECT @@sql_mode;
-- or globally:
SHOW GLOBAL VARIABLES LIKE 'sql_mode';
```
If the output contains ONLY_FULL_GROUP_BY, the strict grouping check is enforced.

Fix the query by adding missing columns to GROUP BY or using an aggregate.

Problematic query:

-- Fails: 'name' is selected but not in GROUP BY
SELECT department_id, name, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

Fix option A — add the column to GROUP BY:

SELECT department_id, name, COUNT(*) AS headcount
FROM employees
GROUP BY department_id, name;

Fix option B — use ANY_VALUE() when the non-determinism is acceptable (e.g., picking any representative name per department):

SELECT department_id, ANY_VALUE(name) AS sample_name, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

Fix option C — use an aggregate that makes the intent explicit:

SELECT department_id, MAX(name) AS last_name_alpha, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

Use a subquery or window function for more complex cases.

When you need a non-aggregated column from a specific row (e.g., the employee with the highest salary per department):

SELECT department_id, name, salary
FROM employees e
WHERE salary = (
    SELECT MAX(salary) FROM employees WHERE department_id = e.department_id
);

Or with a window function (MySQL 8.0+):

SELECT department_id, name, salary
FROM (
    SELECT department_id, name, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;

Temporarily disable ONLY_FULL_GROUP_BY for a session (migration shortcut only — not recommended for production).
```
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
```
To persist the change at the server level (requires privilege and a config reload):
```
# my.cnf / my.ini
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```
Removing ONLY_FULL_GROUP_BY restores MySQL 5.6 behavior — queries will run but may return non-deterministic results. Use this only as a short-term workaround while fixing affected queries.
Check functional dependencies when GROUP BY covers a primary key.

MySQL 5.7.5+ implements the SQL standard notion of functional dependency: if you group by a table's primary key, all other columns of that table are considered functionally dependent and can appear in SELECT without being in GROUP BY. If your query groups by the primary key but still raises error 1055, confirm the column truly belongs to the same table and the primary key constraint exists.
```
-- Grouping by primary key — other columns of the same table are allowed
SELECT id, first_name, last_name, COUNT(orders.id) AS order_count
FROM customers
JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id;  -- valid in MySQL 5.7.5+ with ONLY_FULL_GROUP_BY
```

Additional Information

SQLSTATE 42000 is the "syntax error or access rule violation" class; error 1055 shares this class with other SQL-grammar-level rejections.
The ANY_VALUE() function was introduced in MySQL 5.7.5 specifically to suppress error 1055 for intentionally non-deterministic selections without disabling the entire SQL mode.
Related errors: Error 1140 (ER_MIX_OF_GROUP_FUNC_AND_FIELDS) is raised when aggregate and non-aggregate expressions are mixed in a SELECT without a GROUP BY clause at all.
Frameworks: Laravel's Eloquent, Django ORM, and ActiveRecord all have known quirks where raw groupBy() calls do not automatically adjust the SELECT list. Check ORM-generated SQL with query logging before assuming the ORM handles this correctly.
MySQL 8.0 keeps ONLY_FULL_GROUP_BY in the default SQL mode and adds improved functional-dependency detection, so some queries rejected in 5.7 are accepted in 8.0 if the dependency can be inferred from the schema.

Frequently Asked Questions

Why did my app break after upgrading from MySQL 5.6 to 5.7? MySQL 5.7 added ONLY_FULL_GROUP_BY to the default SQL mode. Queries that previously returned arbitrary (non-deterministic) values for ungrouped columns now raise error 1055 instead. The correct fix is to rewrite the queries; removing ONLY_FULL_GROUP_BY from the SQL mode is a temporary workaround that restores the old unsafe behavior.

What does ANY_VALUE() do and when should I use it? ANY_VALUE(col) tells MySQL that you acknowledge the value is non-deterministic and you are fine with whatever row is returned for that column within the group. Use it when the column's value is genuinely the same for all rows in each group (e.g., a denormalized label), or in exploratory queries where exactness does not matter. Avoid it in reporting or business-logic queries where correctness is required.

Can I fix this without changing the query? If rewriting every affected query is not immediately feasible, you can remove ONLY_FULL_GROUP_BY from the server's sql_mode setting. This unblocks the application but does not fix the underlying non-determinism. Treat it as a temporary measure and track the queries for proper remediation.

Does grouping by primary key exempt all columns from this rule? Yes, in MySQL 5.7.5 and later with ONLY_FULL_GROUP_BY active: if the GROUP BY list includes a table's primary key, all other columns of that same table are considered functionally dependent on it and may appear in SELECT freely. This does not apply to columns from joined tables unless those joined tables are also grouped by their primary keys.

How to Fix MySQL Error 1055: Expression Not in GROUP BY (ONLY_FULL_GROUP_BY)

Impact

Common Causes

Troubleshooting and Resolution Steps

Additional Information

Frequently Asked Questions