Mysql 5.7 GROUP BY error incompatible with sql_mode=only_full_group_by
In mysql 5.7 sql mode ONLY_FULL_GROUP_BY is enabled by default. Which means that, your select list must be in your aggregate(GROUP BY/ Having) function. See the details from here https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
Recently i faced this types of problem, my sql was exactly
SELECT * FROM `users` GROUP BY `user_level_id`
The problem on this query is that, i applied here GROUP BY only user_level_id
, but my select list have all the fields which i mean using *
, if i run the sql as below then it should work
SELECT `user_level_id` FROM `users` GROUP BY `user_level_id`
Cause the select list and GROUP BY list are same. If the sql as below, then it should also work
SELECT COUNT(id) AS total_id, user_level_id FROM `users` GROUP BY `user_level_id`
Now, if you need to get data with more fields, but can’t use them on GROUP BY clause then you can follow this way.
SELECT ANY_VALUE(id) id, user_level_id FROM `users` GROUP BY `user_level_id`