InletDownload

MySQL error 1055

ERROR 1055 (42000): Expression of SELECT list is not in GROUP BY clause (only_full_group_by)

Your query groups rows but also selects a column that can have several values in a group, so MySQL can’t know which one to show. Add the column to GROUP BY, wrap it in an aggregate such as MAX(), or use ANY_VALUE() if any value will do.

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

Tested on MySQL 8.4.11 and MariaDB 11.4.13 · Updated 9 October 2026

What it means

GROUP BY customer_id turns all of a customer’s orders into one result row. A column like status can differ between those orders (paid, refunded), so SELECT customer_id, status … GROUP BY customer_id asks for one value where there are several. With the ONLY_FULL_GROUP_BY SQL mode on, which is MySQL’s default since 5.7.5, the server refuses such a query instead of picking one.

A column is allowed in the select list, HAVING or ORDER BY if it is:

  • listed in GROUP BY,
  • inside an aggregate (MAX(), SUM(), COUNT(), GROUP_CONCAT()…), or
  • functionally dependent on the grouped columns: grouping by a table’s primary key makes every other column of that table single-valued, and MySQL knows it.

The message names the column ('seo_mysql.sales_orders.status') and its position in the select list (Expression #2).

Common causes

  1. A query written for an older server or with the mode turned off, now run on MySQL 5.7.5 or later, where the mode is on.
  2. Selecting a “descriptive” column next to an aggregate, expecting it to be the same in every row of the group.
  3. Trying to get “the latest row per group” with GROUP BY, which never did that reliably.
  4. Code tested on MariaDB, run on MySQL. MariaDB doesn’t turn the mode on by default, so the query works there.

How to fix it

Group by it too

If you want one row per customer and status:

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

Aggregate it

If you want one row per customer, decide what to show for status:

SELECT customer_id, MAX(status), SUM(total) FROM sales_orders GROUP BY customer_id;
SELECT customer_id, GROUP_CONCAT(DISTINCT status ORDER BY status) AS statuses, SUM(total)
FROM sales_orders GROUP BY customer_id;

ANY_VALUE() when any value will do (MySQL only)

When the column is the same throughout each group, or you really don’t mind which value you get:

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

MariaDB 11.4 has no ANY_VALUE(); use MIN() or MAX() there.

Group by the primary key

On MySQL, grouping by the primary key lets you select the table’s other columns:

SELECT s.id, s.name, SUM(o.total)
FROM shoppers s JOIN sales_orders o ON o.customer_id = s.id
GROUP BY s.id;

MariaDB doesn’t detect this, so with ONLY_FULL_GROUP_BY on it still rejects s.name; list it in GROUP BY for code that runs on both.

The latest row per group: use a window function

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

This ran on both MySQL 8.4 and MariaDB 11.4, and returns each customer’s newest order.

Turning the mode off

You can, for one session:

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

The query then runs, but the server “is free to choose any value from each group”, in MySQL’s own words, so results can change with the data or the plan. Fix the query if you can.

Reproduce it

On MySQL 8.4.11 (default sql_mode includes ONLY_FULL_GROUP_BY), customer 1 has a paid and a refunded order:

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

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

Two related errors from the same mode, for an aggregate without GROUP BY and for DISTINCT with an ORDER BY column that isn’t selected:

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'seo_mysql.sales_orders.customer_id'; this is incompatible with sql_mode=only_full_group_by
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'seo_mysql.sales_orders.created_at' which is not in SELECT list; this is incompatible with DISTINCT

ANY_VALUE(status) and GROUP BY s.id with s.name both ran. With the mode removed for the session, the first query ran and showed paid for customer 1.

On MariaDB 11.4.13 (default sql_mode is STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION) the first query ran without complaint. With ONLY_FULL_GROUP_BY added to the session it gave a shorter message, and GROUP BY s.id failed too:

ERROR 1055 (42000): 'seo_mysql.sales_orders.status' isn't in GROUP BY
ERROR 1055 (42000): 'seo_mysql.s.name' isn't in GROUP BY
ERROR 1305 (42000): FUNCTION seo_mysql.ANY_VALUE does not exist

In Inlet

When a query fails, Inlet shows the error with a hint. With your own Anthropic API key, Ask Claude (⌘L) can rewrite the failed statement; it sends the schema and the SQL, never your rows, and Preview shows the exact request first.

Related

Sources