Why Do My Aggregate Queries Without GROUP BY Cause Errors in MySQL 5.7.14 ?

Aggregate Queries Without GROUP BY: Understanding Errors and Resolutions

Previously, on older versions of MySQL, queries without a GROUP BY clause for aggregated functions (e.g., COUNT()) often functioned without issues. However, with MySQL 5.7.14 and upwards, such queries may encounter an error:

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column [...]

This error stems from a change in MySQL's default behavior to align with other RDBMS systems, which require the non-aggregated fields in the SELECT clause to be included in the GROUP BY clause when using aggregated functions.

Addressing the Error:

To resolve this error, you have two options:

1. Adjust MySQL Settings:

You can revert to the legacy behavior by modifying MySQL's settings. However, this is generally not recommended as it perpetuates non-optimal query practices.

2. Fix the Query:

The preferred solution is to modify the query to include non-aggregated columns in the GROUP BY clause. For example, the query above can be rewritten as:

SELECT id, password, COUNT(id) AS count 
FROM users 
WHERE email = :email 
GROUP BY id, password 
LIMIT 1

Exception in 5.7.5 :

In MySQL versions 5.7.5 and later, it is still permissible to exclude non-aggregated columns from the GROUP BY clause when the unaggregated column has been constrained to a single value (e.g., using a filter in the WHERE clause). Details and examples of this exception can be found here.

The above is the detailed content of Why Do My Aggregate Queries Without GROUP BY Cause Errors in MySQL 5.7.14 ?. For more information, please follow other related articles on the PHP Chinese website!