Error related to only_full_group_by when executing a query in MySql

by

I would just add group_id to the GROUP BY.

When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT(), SUM(), MAX() etc… I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by setting by default for good reasons, so it’s best to get used to it and make your queries comply with it.

So why my simple answer above? I’ve made a couple of assumptions:

1) the group_id is unique. Seems reasonable, it is an ‘ID’ after all.

2) the group_name is also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_names and you then follow my advice to add group_id to the GROUP BY, you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily!

It’s also good practice to qualify all the columns with their table name or alias when there’s more than one table involved… 

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
  3. MySQL Query GROUP BY day / month / year
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. Grouping into interval of 5 minutes within a time range
  7. Retrieving the last record in each group – MySQL
  8. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  9. MySQL : isn’t in GROUP BY
  10. Mysql query to dynamically convert rows to columns
  11. MySQL “Group By” and “Order By”
  12. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  13. Is there ANY_VALUE capability for mysql 5.6?
  14. Group mysql query by 15 min intervals
  15. In SQL, how to select the top 2 rows for each group
  16. SQL Query to get column values that correspond with MAX value of another column?
  17. MySQL Group By and Sum total value of other column
  18. Is it possible to GROUP BY multiple columns using MySQL?
  19. Using Count to find the number of occurrences
  20. What does SQL clause “GROUP BY 1” mean?
  21. Using ORDER BY and GROUP BY together
  22. MySQL: How do I join same table multiple times?
  23. GROUP BY – do not group NULL
  24. MySQL GROUP BY DateTime +/- 3 seconds
  25. MySQL Group By Hours
  26. mysql count group by having
  27. SQL query with avg and group by
  28. MySQL – Selecting a Column not in Group By
  29. SQL: Creating a Relational table with 2 different auto_increment
  30. #1060 – Duplicate column name ‘id’

Leave a Comment