Fixing MySQL Error 1055 (ONLY_FULL_GROUP_BY) Without Disabling It

Posted by Kyle Hankinson July 28, 2026

---

Quick answer: error 1055 means your SELECT list contains a column that is neither aggregated nor listed in GROUP BY, so MySQL cannot know which row's value you want. The fix is to change the query, not the server: add the column to GROUP BY, wrap it in an aggregate like MAX(), mark it explicitly arbitrary with ANY_VALUE(), or use a window function if you actually wanted the whole row. Disabling ONLY_FULL_GROUP_BY makes the error disappear and leaves the ambiguity in your results.

Now the longer version, with every example run on MySQL 8.4.10 and MariaDB 11.4.12.

Reproducing the error

Take a small orders table:

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  placed_at DATETIME NOT NULL
);

Ask for spending per customer, and casually include status:

SELECT customer_id, status, SUM(total)
FROM orders
GROUP BY customer_id;

On any default MySQL from 5.7.5 onward this fails:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'shop.orders.status' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

The error is precise once you translate it. Each group here is one customer_id, and a customer can have many orders with different statuses. You asked for one status per group without saying which one. Older MySQL happily picked one for you; ONLY_FULL_GROUP_BY (enabled by default since MySQL 5.7.5, and in every 8.x and 9.x release) refuses to guess.

Every column in the SELECT list must be one of three things: aggregated, listed in GROUP BY, or functionally dependent on the GROUP BY columns. That last one surprises people, so let's start there.

MySQL understands primary keys

Most write-ups of this error skip functional dependency entirely, which leads to painfully over-specified GROUP BY clauses. If you group by a table's primary key, MySQL knows every other column of that table is determined by it, and allows them without aggregation. This runs cleanly on 8.4:

SELECT c.id, c.name, SUM(o.total) AS lifetime_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

id	name	lifetime_total
1	Ana	127.75
2	Ben	104.99
3	Cho	150.00

c.name appears nowhere in the GROUP BY, and there is no error, because grouping by the primary key c.id pins down exactly one name per group. So when a legitimate query trips 1055 after an upgrade, often the cleanest rewrite is to group by the key instead of by a non-unique column. The details are in the MySQL manual's GROUP BY handling page.

The three rewrites for genuinely ambiguous queries

When the column really is ambiguous, pick the rewrite that matches what you meant.

You wanted separate groups. If different statuses should be separate rows, say so:

SELECT customer_id, status, SUM(total) AS subtotal
FROM orders
GROUP BY customer_id, status;

You wanted a specific value. If you meant "the latest" or "the largest", use an aggregate:

SELECT customer_id, MAX(placed_at) AS last_order_at, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

Any value will do. ANY_VALUE() (available since MySQL 5.7) tells the server, explicitly and greppably, that you accept an arbitrary pick:

SELECT customer_id, ANY_VALUE(status) AS some_status, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

This is exactly the old pre-5.7 behavior, but declared in the query where reviewers can see it, instead of hidden in server configuration.

There is a fourth case that produces more 1055 errors than any other: you did not want an aggregate at all, you wanted the whole row that holds the group's maximum. MAX(placed_at) alongside status will not give you the status of the latest order. For that, rank the rows:

SELECT id, customer_id, status, total, placed_at
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders o
) ranked
WHERE rn = 1;

Window functions require MySQL 8.0 or MariaDB 10.2. We cover the pattern in depth in selecting only rows with the max value on a column, and the window function toolbox itself in SQL window functions: SUM, AVG, LAG and LEAD.

What disabling the check actually costs

Here is the part the "just remove ONLY_FULL_GROUP_BY" answers never show. MariaDB does not enable this mode by default in any version, so the ambiguous query from the top runs there without complaint. On MariaDB 11.4 it returned:

customer_id	status	SUM(total)
1	shipped	127.75
2	shipped	104.99
3	pending	150.00

Looks plausible. But in the test data, customer 1's most recent order is pending and customer 2 has a cancelled order; the server simply returned whichever status it encountered first. No error, no warning, just a column of confidently wrong-looking-right values that will shift with storage order, indexes, and optimizer changes. That MariaDB difference is also why an application "works on MariaDB but breaks on MySQL 8": the query was ambiguous on both, and only MySQL said so.

If you maintain legacy code you truly cannot rewrite today, scope the override to your session rather than the server:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Caution: prefer this session form over SET GLOBAL or an option-file change. A global change alters result semantics for every application on the server and silently converts future 1055 errors into indeterminate data. The sql_mode documentation covers scoping rules if you do need broader changes.

Verifying your rewrite

After rewriting, check the numbers, not just the absence of an error: a careless GROUP BY customer_id, status where you meant per-customer totals will happily return different sums. Running the original (under a session override) and the rewrite side by side is the fastest sanity check. In SQLPro for MySQL you can execute both statements in one editor tab and compare the result sets as separate grids, then export either to CSV or JSON if you want to diff them properly.

The one-line takeaway: 1055 is MySQL telling you your query has more than one correct answer. Pick one, write it down in SQL, and the error and the ambiguity disappear together.

---