📋

Description

This error occurs when you execute an aggregated query that includes non-aggregated columns in the SELECT list, but these columns are not present in the GROUP BY clause. It specifically triggers when the `ONLY_FULL_GROUP_BY` SQL mode is active, preventing ambiguous results by enforcing strict SQL standards for grouping.

💬

Error Message

In aggregated query without GROUP BY, expression #%u of %s contains nonaggregated column '%s'; this is incompatible with sql_mode=only_full_group_by

🔍

Known Causes

3 known causes

⚠️

`ONLY_FULL_GROUP_BY` Mode Enabled

MySQL's `ONLY_FULL_GROUP_BY` SQL mode is active (default in recent versions), requiring all non-aggregated columns in the SELECT list to be explicitly included in the GROUP BY clause.

⚠️

Selecting Non-Aggregated Columns

Your query attempts to select columns that are not part of an aggregate function (e.g., COUNT(), SUM(), AVG()) and are also not listed in the GROUP BY clause.

⚠️

Missing or Incomplete GROUP BY Clause

The GROUP BY clause is either entirely absent from your query or does not specify all non-aggregated columns that are present in your SELECT statement.

🛠️

Solutions

4 solutions available

1. Include Non-Aggregated Columns in GROUP BY easy

Modify the GROUP BY clause to include all non-aggregated columns from the SELECT list.

1

Identify all columns in your SELECT statement that are not part of an aggregate function (like SUM(), COUNT(), AVG(), MIN(), MAX()).

2

Add these identified non-aggregated columns to your GROUP BY clause.

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

2. Use Aggregate Functions for Non-Aggregated Columns easy

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

1

Examine your SELECT statement. For any column that is not in your GROUP BY clause and is not intended to be grouped by, wrap it in an appropriate aggregate function.

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

2

Common aggregate functions to consider are MIN(), MAX(), ANY_VALUE(), or even just wrapping it in a GROUP_CONCAT() if you want to see all distinct values.

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

3. Disable ONLY_FULL_GROUP_BY SQL Mode (Quick Fix) medium

Temporarily or permanently disable the ONLY_FULL_GROUP_BY SQL mode to allow queries that violate it.

1

To disable it for the current session, execute the following SQL command:

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

2

To disable it permanently, you need to modify your MySQL configuration file (my.cnf or my.ini). Locate the [mysqld] section and add or modify the sql_mode line. Then, restart the MySQL server.

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3

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

sudo systemctl restart mysql  # Example for systemd-based systems

4. Refactor Query for Clarity and Correctness advanced

Re-evaluate the query's intent and rewrite it to be compliant with SQL standards.

1

Understand the exact data you need to retrieve. If you are grouping by a set of columns, ensure that all other selected columns are either functionally dependent on the grouped columns or are being aggregated.

2

Consider using subqueries or Common Table Expressions (CTEs) to pre-process data or to isolate the grouping logic, making the main query cleaner and more compliant.

WITH RankedData AS (
  SELECT
    column1, column2, column3,
    ROW_NUMBER() OVER(PARTITION BY column2 ORDER BY column1) as rn
  FROM your_table
)
SELECT column1, column2, column3
FROM RankedData
WHERE rn = 1;

3

If the intent is to get a single representative value for non-grouped columns, explicitly choose an aggregation like MIN(), MAX(), or ANY_VALUE().

SELECT column2, MIN(column1) as first_column1, MAX(column3) as last_column3
FROM your_table
GROUP BY column2;

🔗

Related Errors

5 related errors

MySQL Error 3088: Non-Aggregated Column in GROUP BY