How to Resolve MySQL Error #1140: 'Mixing of GROUP Columns'?

MySQL Error #1140: Understanding the "Mixing of GROUP Columns" Issue

The MySQL error #1140, "Mixing of GROUP columns (MIN(), MAX(), COUNT(), ...) with no GROUP columns is illegal if there is no GROUP BY clause," arises when you attempt to aggregate data (e.g., using functions like COUNT()) without grouping the results. This can occur when the SQL query contains aggregate functions and non-aggregate columns in the SELECT clause, and a GROUP BY clause is absent.

Problem Description:

The following SQL query exemplifies this issue:

SELECT COUNT(node.nid), node.nid AS nid, node_data_field_update_date.field_update_date_value AS node_data_field_update_date_field_update_date_value
FROM node node LEFT JOIN content_type_update node_data_field_update_date ON node.vid = node_data_field_update_date.vid
WHERE node.type IN ('update')
ORDER BY node_data_field_update_date_field_update_date_value DESC

Executing this query with the ONLY_FULL_GROUP_BY setting enabled on the server results in the error message:

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause`

Solution:

To resolve this issue, one must either disable the ONLY_FULL_GROUP_BY setting or enforce a grouping operation in the SQL query.

• Disabling ONLY_FULL_GROUP_BY: This can be achieved by altering the MySQL configuration file and setting the following parameter:

only_full_group_by = 0

• Enforcing Grouping: Adding a GROUP BY clause to the query will resolve the issue. In this case, grouping can be applied to one or more fields, such as node.nid, to ensure that the aggregate function (COUNT()) is applied only within the grouped data. The modified query would look like:

SELECT COUNT(node.nid), node.nid AS nid, node_data_field_update_date.field_update_date_value AS node_data_field_update_date_field_update_date_value
FROM node node LEFT JOIN content_type_update node_data_field_update_date ON node.vid = node_data_field_update_date.vid
WHERE node.type IN ('update')
GROUP BY node.nid
ORDER BY node_data_field_update_date_field_update_date_value DESC

By applying either of these solutions, you can bypass the "Mixing of GROUP columns" error and successfully retrieve the aggregated data.

The above is the detailed content of How to Resolve MySQL Error #1140: 'Mixing of GROUP Columns'?. For more information, please follow other related articles on the PHP Chinese website!