📋

Description

This error occurs when MySQL's `only_full_group_by` SQL mode is active, preventing queries from selecting non-aggregated columns that are not included in the `GROUP BY` clause. It signals an ambiguity where MySQL cannot determine which value to return for a column within a grouped set of rows.

💬

Error Message

Expression #%u of %s is not in GROUP BY clause and contains nonaggregated column '%s' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

🔍

Known Causes

4 known causes

⚠️

Selecting Non-Grouped Columns

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

⚠️

Strict SQL Mode Active

The `sql_mode=only_full_group_by` setting is enabled, enforcing stricter SQL standards for GROUP BY queries.

⚠️

Lack of Functional Dependency

A non-aggregated column in the SELECT list is not uniquely determined by the columns specified in the GROUP BY clause.

⚠️

Legacy Query Execution

An older SQL query, designed for MySQL versions without `only_full_group_by` as default, is executed on a modern server.

🛠️

Solutions

4 solutions available

1. Add Missing Columns to GROUP BY Clause easy

Modify your SQL query to include all non-aggregated columns in the GROUP BY clause.

1

Identify the non-aggregated columns in your SELECT list that are causing the error. The error message will usually point these out.

2

Add these identified columns to your GROUP BY clause. Ensure that every column in the SELECT list that is not part of an aggregate function (like COUNT, SUM, AVG, etc.) is present in the GROUP BY clause.

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

2. Use Aggregate Functions for Non-Grouped Columns medium

Wrap non-aggregated columns in an aggregate function if their values are guaranteed to be the same within each group.

1

Examine the non-aggregated columns that are causing the error. Determine if these columns will always have a single, consistent value for each group defined by your existing GROUP BY clause.

2

If the values are consistent, wrap the column in an aggregate function like MIN(), MAX(), or ANY_VALUE(). ANY_VALUE() is often the most appropriate when you just need *a* value from the group, and it's guaranteed to be the same.

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

3

Alternatively, use ANY_VALUE() if available (MySQL 5.7+). This function explicitly states you want any value from the group.

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

3. Temporarily Disable ONLY_FULL_GROUP_BY Mode quick

Disable the `ONLY_FULL_GROUP_BY` SQL mode for the current session or globally.

1

To disable `ONLY_FULL_GROUP_BY` for the current session, execute the following command. This is a temporary fix and will be reset when the session ends.

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

2

To disable `ONLY_FULL_GROUP_BY` globally (requires SUPER privilege), edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`) and remove `ONLY_FULL_GROUP_BY` from the `sql_mode` setting. Then, restart the MySQL server.

# Example my.cnf snippet:
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3

After modifying the configuration file, restart the MySQL server. The command to restart depends on your operating system and MySQL installation method (e.g., `systemctl restart mysql`, `service mysql restart`).

sudo systemctl restart mysql

4. Use Subqueries or CTEs for Complex Grouping advanced

Refactor the query using subqueries or Common Table Expressions (CTEs) to simplify grouping logic.

1

Consider rewriting the query by first selecting the necessary aggregated data in a subquery or CTE, and then joining or selecting from that result. This can help isolate the grouping logic.

WITH AggregatedData AS (
    SELECT
        column1,
        COUNT(column3) as count_of_column3
    FROM
        your_table
    WHERE
        condition
    GROUP BY
        column1
)
SELECT
    ad.column1,
    ad.count_of_column3,
    yt.column2 -- column2 is not aggregated here, but can be handled if it's functionally dependent or selected from the aggregated result
FROM
    AggregatedData ad
JOIN
    your_table yt ON ad.column1 = yt.column1 -- This join might need careful consideration depending on desired output
GROUP BY
    ad.column1, yt.column2; -- Or restructure to avoid this if column2 is not needed per group

2

Alternatively, if you need to select a non-aggregated column that is functionally dependent on the GROUP BY columns, you can often achieve this by selecting it in the outer query after the aggregation is done in a subquery, ensuring the outer query's GROUP BY clause is correct or that the column is selected appropriately.

SELECT
    column1,
    ANY_VALUE(column2) AS column2_value,
    COUNT(column3)
FROM
    your_table
WHERE
    condition
GROUP BY
    column1;

🔗

Related Errors

5 related errors

MySQL Error 3087: GROUP BY Column Mismatch