MySQL Error 1055

Error number

1055

SQLSTATE

42000

Symbol

ER_WRONG_FIELD_WITH_GROUP

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4, where ONLY_FULL_GROUP_BY is enabled by default (wording checked on MySQL 8.0.46)

Category

Query Rules

Last reviewed

24 October 2026

What it means

Error 1055 means a grouped query selects or sorts by a column that can have several different values inside one group, so MySQL cannot know which value to return.

GROUP BY folds many rows into one row per group. Every expression in the SELECT list, HAVING and ORDER BY must then have a single value per group: it is either a grouping column, an aggregate such as MAX() or COUNT(), or a column that is functionally dependent on the grouping columns (for example any column of a table whose primary key is in the GROUP BY). With the ONLY_FULL_GROUP_BY sql_mode, which is on by default, MySQL rejects anything else with 1055.

The message pinpoints the problem: Expression #2 of SELECT list is the second item you selected, and shop.employees.name is the column (database, table, column) that varies within a group. In SELECT dept, name, MAX(salary) ... GROUP BY dept there are several names per department, and the query does not say which one you want. Without the rule, MySQL is free to pick any name from the group, which is why the same query on a server without ONLY_FULL_GROUP_BY can return a name that does not belong to the highest salary at all.

MySQL 8.0 reports this with the long wording shown above under error number 1055 (the text belongs to the newer message ER_WRONG_FIELD_WITH_GROUP_V2). Handle it in code by the number 1055, not by matching the text.

Common causes

• Selecting a descriptive column next to an aggregate, such as SELECT dept, name, MAX(salary), intending "the person with the highest salary". GROUP BY cannot express that; it needs a window function or a join.
• Grouping by an expression (YEAR(hired)) and also selecting another column that is not grouped.
• Sorting a grouped result by a column that is not grouped or aggregated (GROUP BY dept ORDER BY hired).
• Grouping by a column that is not a primary key or a NOT NULL unique key, then selecting other columns from that table. MySQL only treats columns as dependent when a key proves it.
• Code written for a server or session where ONLY_FULL_GROUP_BY was removed from sql_mode, moved to a server that uses the default.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT @@SESSION.sql_mode AS session_mode,
       @@GLOBAL.sql_mode  AS global_mode;

SELECT dept, COUNT(DISTINCT name) AS different_names
FROM employees
GROUP BY dept
HAVING COUNT(DISTINCT name) > 1;

SELECT index_name, column_name, seq_in_index
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'employees'
  AND non_unique = 0
ORDER BY index_name, seq_in_index;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT dept,
       MAX(salary) AS top_salary,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS people
FROM employees
GROUP BY dept;

SELECT dept, name, salary
FROM (
    SELECT dept, name, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) AS ranked
WHERE rn = 1;

SELECT d.id, d.name, COUNT(s.id) AS staff
FROM departments AS d
LEFT JOIN staff AS s ON s.dept_id = d.id
GROUP BY d.id;

SELECT dept, COUNT(*) AS staff, MIN(hired) AS first_hire
FROM employees
GROUP BY dept
ORDER BY first_hire;

Examples

Selecting a name next to MAX() per department

CREATE TABLE employees (
    id     INT PRIMARY KEY,
    name   VARCHAR(100),
    dept   VARCHAR(50),
    salary DECIMAL(10,2),
    hired  DATE
);

SELECT dept, name, MAX(salary)
FROM employees
GROUP BY dept;

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

Ordering grouped rows by a column that is not grouped

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), dept VARCHAR(50), salary DECIMAL(10,2), hired DATE);

SELECT dept, COUNT(*)
FROM employees
GROUP BY dept
ORDER BY hired;

ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'shop.employees.hired' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Grouping by an expression and selecting another column

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), dept VARCHAR(50), salary DECIMAL(10,2), hired DATE);

SELECT YEAR(hired) AS yr, dept, COUNT(*)
FROM employees
GROUP BY YEAR(hired);

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

How to prevent it

Before writing a GROUP BY, say in words what one output row represents ("one row per department") and make every selected column either part of that grain or an aggregate over it. When you need the details of a particular row in each group (the latest order, the top earner), reach for a window function or a join rather than GROUP BY.

Keep ONLY_FULL_GROUP_BY enabled in every environment, including development, so ambiguous queries fail early instead of returning arbitrary values in production. Removing it from sql_mode, or wrapping columns in ANY_VALUE() to silence the error, does not make the query correct: MySQL is then free to return any value from each group, so the result is not deterministic. ANY_VALUE() is only appropriate when you have proved, for example with the diagnostic above, that the value is the same for every row in a group.

Common questions

Learn more on CodeWithSQL

• ORDER BY, GROUP BY and HAVING explained
• SQL aggregate functions explained
• SQL window functions guide

Official documentation

• MySQL handling of GROUP BY
• Detection of functional dependence
• Server SQL modes (ONLY_FULL_GROUP_BY)
• MySQL 8.0 server error message reference

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.