📋

Description

MySQL Error 1055 occurs when a SELECT statement includes non-aggregated columns that are not specified in the GROUP BY clause. This typically happens when the `ONLY_FULL_GROUP_BY` SQL mode is enabled, enforcing standard SQL behavior for aggregate queries. It means every column in your SELECT list must either be part of an aggregate function or explicitly listed in the GROUP BY clause.

💬

Error Message

'%s' isn't in GROUP BY

🔍

Known Causes

4 known causes

⚠️

Strict SQL Mode Enabled

The `ONLY_FULL_GROUP_BY` SQL mode is active, requiring all non-aggregated columns in the SELECT list to be explicitly included in the GROUP BY clause.

⚠️

Incomplete GROUP BY Clause

The GROUP BY clause in your query omits one or more non-aggregated columns that are present in the SELECT statement.

⚠️

Misunderstanding Aggregation

Attempting to select individual row values alongside aggregated results without properly defining the grouping for all non-aggregated columns.

⚠️

Wildcard Selection with Group By

Using `SELECT *` with a `GROUP BY` clause, implicitly trying to include all columns, which can violate the `ONLY_FULL_GROUP_BY` rule.

🛠️

Solutions

4 solutions available

1. Add Non-Grouped Columns to GROUP BY Clause easy

Include all non-aggregated columns from the SELECT list in your GROUP BY clause.

1

Identify the column(s) mentioned in the error message that are not part of an aggregate function (like SUM, COUNT, AVG, MAX, MIN).

2

Modify your `SELECT` statement to include these identified columns in the `GROUP BY` clause.

SELECT column1, column2, COUNT(*) FROM your_table GROUP BY column1, column2;

2. Use Aggregate Functions for Non-Grouped Columns easy

Apply an aggregate function to any columns in the SELECT list that are not in the GROUP BY clause.

1

Examine your `SELECT` statement and identify columns that are selected but not included in the `GROUP BY` clause and are not aggregated.

2

Choose an appropriate aggregate function (e.g., `MAX()`, `MIN()`, `ANY_VALUE()`, `GROUP_CONCAT()`) for each of these columns and apply it in the `SELECT` list.

SELECT column1, MAX(column2) FROM your_table GROUP BY column1;

3

If you intend to select a single arbitrary value from the non-grouped column for each group, `ANY_VALUE()` is a good choice.

SELECT column1, ANY_VALUE(column2) FROM your_table GROUP BY column1;

3. Disable ONLY_FULL_GROUP_BY Mode (Temporary or Global) medium

Temporarily or permanently disable the ONLY_FULL_GROUP_BY SQL mode.

1

To disable it for the current session, run the following command before your query:

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

2

To disable it globally (affects all new connections), edit your MySQL configuration file (`my.cnf` or `my.ini`) and add or modify the `sql_mode` parameter under the `[mysqld]` section. After editing, restart the MySQL server.

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3

Caution: Disabling `ONLY_FULL_GROUP_BY` can lead to ambiguous results if not used carefully, as MySQL will pick an arbitrary value from the non-grouped column. It's generally recommended to fix the query rather than disable this mode.

4. Use DISTINCT within Aggregate Functions easy

If you need to count distinct values within a group, use `COUNT(DISTINCT ...)`.

1

If your intention is to count unique occurrences of a non-grouped column within each group, modify your aggregate function.

SELECT column1, COUNT(DISTINCT column2) FROM your_table GROUP BY column1;

🔗

Related Errors

5 related errors

MySQL Error 1055: Non-Grouped Column in SELECT