MySQL : isn't in GROUP BY

Question

The site produces results, but with SELECT COUNT and SELECT query with GROUP BY having two different result counts. This is likely due to the error that is displaying in phpmyadmin but not on the site.

The Queries:

SELECT count(DISTINCT `name`) as `numrows` FROM `users` WHERE `verified` = '1'

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

PhpMyAdmin provides the following error:

1055 - 'main.users.type' isn't in GROUP BY

When reading MySQL docs, I'm still unclear what it is I have to fix. I can't seem to grasp this.

The first query is doing an implicit grouping by name. The second, wrote a similar way would be something like: SELECT `name` FROM `users` WHERE `verified` = '1' GROUP BY `name` ORDER BY COUNT(*) DESC LIMIT 0, 25 — Jordan Parker, Sep 12 '14 at 03:56
I'm not sure what you're implying. With the query: SELECT name,type,language FROM synset WHERE verified = '1' GROUP BY name ORDER BY COUNT(*) DESC LIMIT 0, 25 The same error would occur. type isn't in Group By If I add type and language, the error disappears. Is this due to an upgrade to MySql maybe? SELECT name,type,language FROM synset WHERE verified = '1' GROUP BY name,type,language ORDER BY COUNT(*) DESC LIMIT 0, 25 will work fine. — James Cordeiro, Sep 12 '14 at 04:13
terrible query time when adding all fields to the group by, obviously — James Cordeiro, Sep 12 '14 at 04:16
Hi @James - any columns/expressions used in either the SELECT or ORDER BY clauses must be contained in the GROUP BY clause if they are not being aggregated (COUNT, SUM, etc). That is why you were getting the error - you were selecting the columns type, language, and code but they were not in the GROUP BY clause (as the accepted answer shows). If there is a setting in MySQL to automatically group, I would be extremely cautious of using that personally. — Jordan Parker, Sep 12 '14 at 05:41
@JordanParker: "*If there is a setting in MySQL to automatically group*" - that is actually the default behaviour. James apparently enabled the `ONLY_FULL_GROUP_BY` option. Otherwise the statement would have just returned "random" results (MySQL doesn't call it random, they call it "indeterminate") http://www.percona.com/blog/2006/09/06/wrong-group-by-makes-your-queries-fragile/ — , Sep 12 '14 at 06:07
@a_horse_with_no_name Wow I had no idea about how deep this goes with 92 vs 99 SQL standard - thanks for the info! http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html — Jordan Parker, Sep 12 '14 at 06:15
If there are two users with the same `name` but different `type`, `language` or `code`, what values do you expect to get for these columns? The query is invalid. — axiac, Oct 23 '17 at 05:43

score 49 · Accepted Answer · answered Sep 12 '14 at 04:20

49

You need to have a full group by:

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

SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause. MySQL by default allows for partial group by and this may produce non-deterministic answers, example:

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;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+

I.e. a random y is select for the group x. One can prevent this behavior by setting @@sql_mode:

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

answered Sep 12 '14 at 04:20

Lennart - Slava Ukraini

6,936
1
20
32

1

I was just reading the ONLY_FULL_GROUP_BY :) The problem with this group by requiring all columns, is the speed of the query. But - this is the correct answer. Can set the sql_mode, in my case I don't need to prevent the behavior. In other cases, this is good to know and good to follow. – James Cordeiro Sep 12 '14 at 04:25
Great answer, and great partial-group-by example helps a lot! Big thanks! – Fengya Li Dec 24 '16 at 00:01
1

This is a dramatically different query to that of OP's. Adding all the columns is the nuclear option of resolving an ambiguous group by. – Caleth Sep 25 '17 at 16:25
Not a "random" aggregate, an *appropriate* aggregate. @O. Jones has a good suggestion. – Caleth Sep 25 '17 at 17:45
You aren't selecting rows, you are selecting aggregations of rows. That's what group by *means* – Caleth Sep 25 '17 at 23:17

score 14 · Answer 2 · edited Sep 25 '17 at 15:34

14

The best solution to this problem is, of course, using a complete GROUP BY expression.

But there's another solution that works around the ONLY_FULL_GROUP_BY blocking of the old MySQL extension to GROUP BY.

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

ANY_VALUE() explicitly declares what used to be implicit in MySQL's incomplete GROUP BY operations -- that the server can choose, well, any, value to return.

edited Sep 25 '17 at 15:34

Graham

7,431
18
59
84

answered Dec 21 '16 at 14:52

O. Jones

103,626
17
118
172

Thanks, worked for me too! If someone wishes all the options to handle that error, here they are: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value – Juri Sinitson Oct 23 '17 at 04:14
this works but only use when you really know that the ANY_VALUE column have same value so it can capture one easily and you get correct results when using with GROUP BY and you really know that only the GROUP BY column have to be specific. – MR_AMDEV May 08 '19 at 00:18
This really helps more than every solution above. Where I'm on a shared hosting cPanel and there's no way to change the sql_mode, I've had to look for workarounds that don't mess up my queries. This way, you keep your query and simply add ```ANYVALUE(cellname) cellname``` and voila it works. – ArabianMaiden Jan 10 '22 at 14:58

Juri Sinitson · Answer 3 · 2017-10-24T14:10:33.870

1

Another solution mentioned multiple times above is to turn off that annoying 'ONLY_FULL_GROUP_BY' e.g. like in this post: Disable ONLY_FULL_GROUP_BY

I think this solution is very useful if you do not want to refactor the whole project for multiple hours. And if you do not care about the unpredictable values of the columns which are not list of the GROUP BY.

edited Oct 24 '17 at 14:10

answered Oct 23 '17 at 05:08

Juri Sinitson

1,445
1
14
18

Disabling `ONLY_FULL_GROUP_BY` forces MySQL to accept invalid SQL `GROUP BY` queries. As a consequence, the [values it returns for the columns that are not in the `GROUP BY` clause are indeterminate](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html). This means the query can and is allowed to return different results using the same input data. – axiac Oct 23 '17 at 05:47
@axiac Sure I've taken it into account, that the columns not mentioned in the GROP BY statement will return some unpredictable values. If I need e.g. the entries grouped by land mark and don't care about id's, than disabling ONLY_FULL_GROUP_BY might be most reasonable. E.g. if you have a legacy ORM which appends that id into SELECT automatically no matter if you want it. So I would be glad, if leave my answer by 0. – Juri Sinitson Oct 23 '17 at 12:04

score 0 · Answer 4 · answered Aug 31 '18 at 21:18

First thing is to see why it is like that. Earlier versions allowed us to be negligent a bit: while the values of the grouped column are completely listed (one from each without exceptions) in the result, values of the other selected column(s) are more or less omitted - but the code can't (does not want to) tell which ones on your behalf:

- Hey, MYSQL, list the families of the street - but add a first name as well to every row.
- Okay sir, but shall I use the father's or the mother's or...? I'm a machine, give exact orders!

Now that we understand the why, we can decide if we have preferences regarding the other column(s). Use

MAX() AS
MIN() AS
etc, works with strings, too

or if it's really all the same, e.g. there's no difference between the nonaggregated values that are related to the grouped one, use

ANY_VALUE() AS

Either way, you let mysql know that you're aware of being "equivocal" but you really don't want to focus on all the columns other than the one(s) you grouped.

score -3 · Answer 5 · answered Nov 11 '17 at 00:32

-3

Just disable strictness for the query.

answered Nov 11 '17 at 00:32

kjdion84

9,552
8
60
87

MySQL : isn't in GROUP BY

5 Answers5

Linked

Related