Select list contains nonaggregated column

Question

Since updating MySQL I've noticed the following query fails

SELECT u.*, p.name as plan, COUNT(u.id) as totalprojects FROM users u LEFT JOIN plans p ON p.id = access LEFT JOIN maps m ON m.user_id = u.id WHERE u.email = 'john@doe.com'

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'kontakt.u.id'; this is incompatible with sql_mode=only_full_group_by

Does anyone know how to get this query fixed to sort the error?

Have a look at http://stackoverflow.com/questions/23921117/disable-only-full-group-by — Chris Lear, Oct 19 '16 at 15:46
@ChrisLear I tried this method but after I restart mysql it still doesn't work. I also tried editing my .cnf file but mysql fails to restart / start afterwards — , Oct 19 '16 at 16:09

score 7 · Answer 1 · answered Jan 04 '19 at 13:05

7

the easiest solution is ANY_VALUE function: ANY_VALUE official documentation

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

answered Jan 04 '19 at 13:05

Shadi Akil

373
3
11

score 3 · Accepted Answer · answered Oct 20 '16 at 01:57

On my Node server with Digital Ocean the conference file I needed to edit was located /etc/mysql/mysql.conf.d/mysqld.cnf

Adding in the following

[mysqld] sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And then service mysql restart fixes the issue.

score 2 · Answer 3 · answered Oct 19 '16 at 15:56

You can do one of two things:

1) change your query so that everything in the select clause is aggregated. Something like this

SELECT u.email, p.name as plan, COUNT(u.id) as totalprojects FROM users u LEFT JOIN plans p ON p.id = access LEFT JOIN maps m ON m.user_id = u.id WHERE u.email = 'john@doe.com' group by u.email;

2) Change the sql mode to allow mysql to run your query. Something like this

[mysqld]
sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

in your my.cnf file.

Note that this sets various sql_mode options. You can read about them here: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

When I try Step 2 mysql won't restart or start. It's a Node server running on Ubuntu — , Oct 19 '16 at 16:15

Select list contains nonaggregated column

3 Answers3

Linked