How to Resolve the MySQL 5.7 `only_full_group_by` Error?

only_full_group_by Error in MySQL 5.7

MySQL version 5.7 introduced the only_full_group_by mode, which requires that all columns that are not aggregated in the SELECT statement must be included in the GROUP BY statement. This error occurs when the query's SELECT statement contains unaggregated columns that do not appear in the GROUP BY statement.

There are two ways to solve this problem:

Method 1: Add missing columns to GROUP BY

Add the missing column mod_users_groups.group_id to the GROUP BY statement:

<code class="language-sql">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</code>

This ensures that all columns that are not aggregated in the SELECT statement are included in the GROUP BY statement, eliminating the error.

Method 2: Use aggregate function

Alternatively, if the missing column is not relevant to the grouping, you can use an aggregate function to summarize the data before selecting it. For example, if group_name is indeed unique, you can replace mod_users_groups.group_id with the following aggregate function:

<code class="language-sql">MIN(mod_users_groups.group_id)</code>

This will return the smallest group_name for each unique group_id, ensuring that the SELECT statement only contains aggregate columns.

The above is the detailed content of How to Resolve the MySQL 5.7 `only_full_group_by` Error?. For more information, please follow other related articles on the PHP Chinese website!