How to Solve MySQL's 'Mixing of GROUP Columns' Error (Error #1140)?

Resolving "Mixing of GROUP Columns" Error in MySQL

The #1140 error in MySQL, indicating a "Mixing of GROUP columns," arises when a query contains non-aggregated (non-GROUP) columns in the SELECT clause while also including aggregated columns without a GROUP BY clause. To address this issue, there are two main approaches:

1. Disabling ONLY_FULL_GROUP_BY:

MySQL has a configuration setting called ONLY_FULL_GROUP_BY, which defaults to ON. This setting enforces strict GROUP BY semantics, requiring all non-aggregated columns in the SELECT clause to be included in the GROUP BY list. To disable this setting, execute the following query:

SET SESSION ONLY_FULL_GROUP_BY = OFF;

Disable ONLY_FULL_GROUP_BY to allow the query to execute without modification.

2. Adding GROUP BY Columns:

Alternatively, you can modify the query to include all selected fields in the GROUP BY clause. This ensures that all non-aggregated columns are "grouped" and that the result set satisfies the strict GROUP BY semantics:

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, node_data_field_update_date.field_update_date_value
ORDER BY node_data_field_update_date_field_update_date_value DESC;

Modifying the query to include the GROUP BY clause solves the issue by explicitly grouping the result set on all non-aggregated columns.

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