📋

Description

This error indicates that a column selected in your `SELECT` statement is not part of the `GROUP BY` clause and is also not used within an aggregate function. MariaDB's default `ONLY_FULL_GROUP_BY` SQL mode enforces this standard SQL rule to prevent ambiguous results. It typically occurs when trying to display non-grouped data alongside grouped aggregates.

💬

Error Message

'%s' isn't in GROUP BY

🔍

Known Causes

3 known causes

⚠️

Non-Aggregated Column Selected

A column is included in the `SELECT` list that is neither explicitly part of the `GROUP BY` clause nor an argument to an aggregate function.

⚠️

Implicit Grouping Violation

The query attempts to select a column whose value is not uniquely determined for each group, leading to ambiguous results under standard SQL.

⚠️

Missing Aggregate Function

A non-grouping column intended to represent a summary (e.g., total, count) is selected directly instead of being wrapped in an appropriate aggregate function.

🛠️

Solutions

3 solutions available

1. Include Non-Aggregated Columns in GROUP BY Clause easy

Add all columns from the SELECT list that are not part of an aggregate function to the GROUP BY clause.

1

Examine your `SELECT` statement. Identify all columns that are not enclosed within an aggregate function (like `COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()`).

2

Modify your `GROUP BY` clause to include all these identified non-aggregated columns. The order of columns in `GROUP BY` doesn't strictly matter for correctness, but it can affect performance in some cases.

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

2. Utilize Aggregate Functions for All Non-Grouped Columns easy

Apply an aggregate function to any column in the SELECT list that you don't want to group by.

1

Review your `SELECT` statement. If you have a column that is not in the `GROUP BY` clause and is also not an aggregate function, you need to decide how to handle it.

2

If you want to see a representative value for that column within each group, apply an aggregate function. Common choices are `MAX()` or `MIN()` to pick one value from the group, or `ANY_VALUE()` (available in MariaDB 10.2+) which is explicitly designed to return an arbitrary value from the group.

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

3

Alternatively, if `ANY_VALUE()` is available and suitable for your needs, use it.

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

3. Disable ONLY_FULL_GROUP_BY Mode (Use with Caution) medium

Temporarily or permanently disable the `ONLY_FULL_GROUP_BY` SQL mode, which enforces stricter `GROUP BY` rules. This is a quick fix but might hide underlying data integrity issues.

1

Check the current `sql_mode` setting for your MariaDB server.

SHOW VARIABLES LIKE 'sql_mode';

2

If `ONLY_FULL_GROUP_BY` is present in the output, you can temporarily disable it for your current session.

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3

To disable it permanently, you need to modify the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). Locate or add the `[mysqld]` section and set `sql_mode`.

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

4

After modifying the configuration file, restart the MariaDB service for the changes to take effect.

# For systemd-based systems (e.g., Ubuntu 15.04+, CentOS 7+)
sudo systemctl restart mariadb

# For SysVinit-based systems (older systems)
sudo service mariadb restart

🔗

Related Errors

5 related errors

MariaDB Error 1055: Missing Column in GROUP BY