Why is my MySQL 5.7 query with aggregation failing without a GROUP BY clause?

Aggregated Query Requires GROUP BY in MySQL 5.7

Q: After upgrading to MySQL 5.7.14, I encounter an error when executing a query without a GROUP BY clause. The query, which involves an aggregation (COUNT) and a non-aggregated column in the SELECT list, previously worked on an older machine.

A: In MySQL version 5.7.5 and later, the default behavior has changed to enforce the use of GROUP BY when aggregating data. This means that when using functions like COUNT in the SELECT clause, all non-aggregated columns must be included in the GROUP BY clause.

To resolve the error, you have two options:

• Modify MySQL settings: Adjust the MySQL configuration to revert to the previous behavior that allowed non-aggregated columns in aggregated queries without GROUP BY.
• Fix the query: Update the query to include all non-aggregated columns in the GROUP BY clause, as in the following example:

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

It's important to note that an exception exists in MySQL 5.7.5 and above when the non-aggregated column has been restricted to a single value using filters in the WHERE clause. In such cases, the non-aggregated column can be excluded from the GROUP BY clause. For more details, refer to the official documentation.

The above is the detailed content of Why is my MySQL 5.7 query with aggregation failing without a GROUP BY clause?. For more information, please follow other related articles on the PHP Chinese website!