How to Resolve MySQL's 'isn't in GROUP BY' Error?

MySQL "isn't in GROUP BY" Error

This error arises when you include non-aggregated columns in the SELECT clause of a query with a GROUP BY clause that do not meet the group by requirements. In this case, the query:

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

triggers the error because type, language, and code are not included in the GROUP BY. To resolve this, you need to incorporate all non-aggregated columns into the group by requirements:

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

SQL Group By Requirements

SQL92 dictates that every column referenced outside of aggregation functions in the SELECT clause must be included in the GROUP BY clause. However, SQL99 relaxes this requirement to state that these columns must be functionally dependent on the group by clause.

MySQL Group By Behavior

MySQL allows for partial group by by default, which can result in non-deterministic results. For instance, consider the following query:

create table t (x int, y int);
insert into t (x,y) values (1,1),(1,2),(1,3);
select x,y from t group by x;

This query may return a random y value for each x group, as demonstrated in the following output:

+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+

Preventing Partial Group By

To prevent this indeterminate behavior, you can set @@sql_mode to 'ONLY_FULL_GROUP_BY':

set @@sql_mode='ONLY_FULL_GROUP_BY';
select x,y from t group by x; 
ERROR 1055 (42000): 'test.t.y' isn't in GROUP BY

The above is the detailed content of How to Resolve MySQL's 'isn't in GROUP BY' Error?. For more information, please follow other related articles on the PHP Chinese website!