Why Does MySQL Throw an 'isn't in GROUP BY' Error?

MySQL's "isn't in GROUP BY" Error: A Comprehensive Explanation

When running a MySQL query that combines a SELECT COUNT and a SELECT with GROUP BY, it's possible to encounter a discrepancy in the number of results. The reason behind this is often the infamous error message, "'field_name' isn't in GROUP BY".

To understand the error, let's break down the issue:

MySQL and GROUP BY

GROUP BY groups rows together based on the values of one or more specified columns. In our case, the query with GROUP BY has clauses like GROUP BY name. This means only rows with unique name values will be grouped.

SQL92 vs. SQL99 vs. MySQL

SQL92 originally required all columns in the SELECT clause to also be in the GROUP BY clause. However, SQL99 relaxed this restriction, allowing for columns functionally dependent on the GROUP BY columns to be included in the SELECT clause.

MySQL, by default, allows for "partial group by," meaning it permits columns in the SELECT clause that aren't explicitly in the GROUP BY clause. However, this can lead to non-deterministic results.

The Solution

To resolve the issue, ensure that all columns in the SELECT clause are either part of the GROUP BY clause or functionally dependent on it. In our example, type, language, and code need to be added to the GROUP BY clause:

SELECT `name`, `type`, `language`, `code` 
FROM `users` 
WHERE `verified` = '1' 
GROUP BY `name`, `type`, `language`, `code` 
ORDER BY `count` DESC LIMIT 0, 25

Full Group By vs. Partial Group By

MySQL also offers a setting, @@sql_mode, which can be set to enforce full group by:

set @@sql_mode='ONLY_FULL_GROUP_BY';

With this setting, any query that doesn't have a full group by will result in an error.

The above is the detailed content of Why Does MySQL Throw an 'isn't in GROUP BY' Error?. For more information, please follow other related articles on the PHP Chinese website!