Why Am I Getting the \'Aggregated Query Without GROUP BY\' Error in MySQL 5.7.5 ?

SQL Error: "Aggregated Query Without GROUP BY"

MySQL 5.7.14 introduces a change that may cause your existing queries to fail with the error "SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY." This error occurs when you aggregate a column (e.g., COUNT()) in the SELECT clause but do not include non-aggregated columns in the GROUP BY clause.

Why the Error Occurs

In earlier versions of MySQL, aggregating columns without using a GROUP BY was allowed. However, to ensure data integrity, MySQL 5.7.5 and later versions enforce a "strict" or "only_full_group_by" SQL mode, which requires all non-aggregated columns to be included in the GROUP BY clause.

Solution Options

You have two options to resolve this issue:

1. Change MySQL Settings

You can modify MySQL's configuration to default to the legacy behavior that allows non-aggregated columns outside of the GROUP BY clause. This is not recommended as it goes against industry best practices and can lead to incorrect results.

2. Fix the Query

Modify your query to include the non-aggregated columns in the GROUP BY clause. For example:

<code class="sql">SELECT id, password, COUNT(id) AS count
FROM users
WHERE email = :email
GROUP BY id, password
LIMIT 1</code>

Exception to the Rule

Note that excluding non-aggregated columns from the GROUP BY clause is still permitted in MySQL 5.7.5 and later versions if the non-aggregated column is limited to a single value. For instance, a filter in the WHERE clause would restrict the column to a single row, making it safe to exclude it from the GROUP BY clause.

The above is the detailed content of Why Am I Getting the \'Aggregated Query Without GROUP BY\' Error in MySQL 5.7.5 ?. For more information, please follow other related articles on the PHP Chinese website!