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

MySQL Error #1140: Mixing of GROUP Columns

This error occurs when a MySQL query mixes GROUP functions with non-GROUP columns in the SELECT list, but without a GROUP BY clause. In other words, it suggests that some of the selected columns should be grouped together, while others should not.

Consider the following SQL query:

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;

This query is attempting to count the number of nodes of type 'update' and retrieve the node ID (nid) and update date for each node. However, it mixes the GROUP function COUNT() with the non-GROUP columns nid and node_data_field_update_date_field_update_date_value in the SELECT list.

To resolve this error, either disable the ONLY_FULL_GROUP_BY setting on the MySQL server or modify the query to group the non-GROUP columns:

Disabling ONLY_FULL_GROUP_BY:

mysql> SET GLOBAL ONLY_FULL_GROUP_BY = OFF;

Adding Grouping to the Query:

SELECT COUNT(node.nid) AS node_count,
       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, node_data_field_update_date.field_update_date_value
ORDER BY node_data_field_update_date_field_update_date_value DESC;

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!