MySQL 5.7 -> Getting Error when trying to GROUP BY? Try this (Part I)

Tony Mucci
Jul 25, 2017 · 2 min read

If you are getting an error that is something similar to this:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘support_desk.mod_users_groups.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is because MySQL 5.7 is now defaulted to ONLY_FULL_GROUP_BY turned on (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by). Which means its a two-step group. For example if I had the following records:

ID    Name
-- ----
1 Bob
2 Frank
3 Jim
4 Bob
5 Jim

And I wanted to group by Name, it would be:

ID    Name
-- ----
1 Bob
4 Bob
3 Jim
5 Jim
2 Frank

The question that the MySQL server asks now is “Ok, I grouped it by name, but WHICH group should I show first? Bob? Jim? or Frank? This is why you need to include which order the groups are retrieved in the result set.

More info:

A workaround that I performed was simply also grouping by the auto-incremented ID column. That seemed to fixed my problem. I would assume because that column is less ambiguous than the other column I was trying to group by.

Happy Coding.

Tony Mucci

Written by

Co-Founder of Eklect Enterprises • Founder of My Company Tools • Co-Founder of DREE

Code Kings

Proramming tips & tricks from professionals

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade