MySQL ONLY_FULL_GROUP_BY Error: Why It Happens

You wrote a query that groups, and MySQL answered with a long error:

> Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column … which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is ONLY_FULL_GROUP_BY, part of MySQL’s default sql_mode. It protects you from ambiguous results.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

The problem

query.sql

SQL

1
2
3

SELECT department, name, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Output

OUTPUT

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

There is one row per department, but a department has several employees. Which name should that row show? MySQL cannot know. In the old forgiving mode, it picked an arbitrary name, and the result looked fine while being wrong.

Fix 1: remove the extra column

If you do not need it, take it out:

query.sql

SQL

SELECT department, ROUND(AVG(salary), 0) AS avg_salary FROM employees GROUP BY department ORDER BY department;

Output

OUTPUT

+-------------+------------+
| department  | avg_salary |
+-------------+------------+
| engineering |     101000 |
| management  |     150000 |
| sales       |      66000 |
| support     |      52000 |
+-------------+------------+
4 rows in set

Fix 2: aggregate it

Say what you mean: the lowest-paid name, the highest salary, a list of names:

query.sql

SQL

SELECT department, MIN(name) AS first_name_alphabetically, GROUP_CONCAT(name ORDER BY name) AS everyone
FROM employees
GROUP BY department
ORDER BY department;

Output

OUTPUT

+-------------+---------------------------+-------------------------------------+
| department  | first_name_alphabetically | everyone                            |
+-------------+---------------------------+-------------------------------------+
| engineering | Ben Carter                | Ben Carter,Chitra Nair,Dev Malhotra |
| management  | Asha Rao                  | Asha Rao                            |
| sales       | Esha Gupta                | Esha Gupta,Farid Khan,Gita Menon    |
| support     | Hari Iyer                 | Hari Iyer                           |
+-------------+---------------------------+-------------------------------------+
4 rows in set

Fix 3: group by that column too

If the group should be “department and name” (which makes one row per employee here), say it:

query.sql

SQL

SELECT department, name, salary FROM employees GROUP BY department, name, salary ORDER BY department, name LIMIT 3;

Output

OUTPUT

+-------------+--------------+-----------+
| department  | name         | salary    |
+-------------+--------------+-----------+
| engineering | Ben Carter   | 120000.00 |
| engineering | Chitra Nair  |  95000.00 |
| engineering | Dev Malhotra |  88000.00 |
+-------------+--------------+-----------+
3 rows in set

(That is not a real summary any more, only an example of the syntax.)

Fix 4: the row you really want, with a window function or a join

If you want “the name of the best-paid employee of each department”, GROUP BY is the wrong tool. Use a window function (ROW_NUMBER), or join to the per-group maximum (Top-N).

Functional dependence: when it is allowed

MySQL accepts a non-grouped column if it is functionally dependent on the grouped columns, that is, guaranteed to have one value per group. The classic case is grouping by the primary key:

query.sql

SQL

SELECT e.id, e.name, e.department, COUNT(*) AS rows_in_group
FROM employees e
GROUP BY e.id
ORDER BY e.id
LIMIT 3;

Output

OUTPUT

+----+-------------+-------------+---------------+
| id | name        | department  | rows_in_group |
+----+-------------+-------------+---------------+
|  1 | Asha Rao    | management  |             1 |
|  2 | Ben Carter  | engineering |             1 |
|  3 | Chitra Nair | engineering |             1 |
+----+-------------+-------------+---------------+
3 rows in set

name and department are determined by id, so MySQL accepts them. The same works after a join, when you group by the primary key of the table whose columns you select:

query.sql

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total INT);
INSERT INTO customers VALUES (1, 'Maya'), (2, 'Rohan');
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 50), (3, 2, 70);

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

Output

OUTPUT

+----+-------+-------+
| id | name  | spent |
+----+-------+-------+
|  1 | Maya  |   150 |
|  2 | Rohan |    70 |
+----+-------+-------+
2 rows in set

c.name is not in the GROUP BY and not aggregated, but it depends on c.id. MySQL 5.7.5 and later detects this. Older versions and some other databases require GROUP BY c.id, c.name. Writing both is harmless, and more portable.

ANY_VALUE: “I do not care which”

When a column really can differ inside a group but you do not care which value is shown, say so with ANY_VALUE():

query.sql

SQL

SELECT department, ANY_VALUE(name) AS some_member, COUNT(*) AS members FROM employees GROUP BY department ORDER BY department;

Output

OUTPUT

+-------------+-------------+---------+
| department  | some_member | members |
+-------------+-------------+---------+
| engineering | Ben Carter  |       3 |
| management  | Asha Rao    |       1 |
| sales       | Esha Gupta  |       3 |
| support     | Hari Iyer   |       1 |
+-------------+-------------+---------+
4 rows in set

It satisfies the mode and documents the decision. Use it with care: the result may change from run to run.

Do not turn the mode off

Disabling ONLY_FULL_GROUP_BY makes the error go away and puts the wrong-result risk back. If an old application relies on it, fix the queries. If you cannot, restrict the change to that application’s session, not the server:

query.sql

SQL

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

This is a temporary workaround, not a solution (SQL Mode).

How to read the error

Find “Expression #N”: it is the N-th column of the SELECT list.
The message names the column, for example employees.name.
Ask: “Is it one value per group?” If yes, group by it or its key. If no, aggregate it or remove it.

lightbulb

Why this is a good thing

The error forces you to think about what each column of the result means. Many “mysterious” wrong reports on old MySQL servers came from exactly this ambiguity.