MySQL #1140 - Mixing of GROUP columns

Question

Hi wondering if perhaps someone could shed some light on the below error. The sql works fine locally but i get the the below error remotely.

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

MySQL said:

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

Answer 1

stripped it back to basics:

   SELECT COUNT( node.nid ) 
     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

Answer 2

Your server probably has ONLY_FULL_GROUP_BY turned on. You can either turn it off or add each of your selected fields to the group by.

Answer 3

The reason a single column using an aggregate function works while the version with columns not using aggregate functions doesn't is because you need to specify a GROUP BY clause. Here's what your query should look resemble:

   SELECT COUNT(n.nid), 
          n.nid, 
          ctu.field_update_date_value
     FROM NODE n
LEFT JOIN CONTENT_TYPE_UPDATE ctu ON ctu.vid = n.vid
    WHERE n.type IN ('update')
 GROUP BY n.nid, ctu.field_update_date_value
 ORDER BY field_update_date_value DESC

I changed out your table aliases for shorter ones - easier to read. Here's the meat of your issue:

   SELECT n.nid,
          COUNT(n.fake_example_column),                
          ctu.field_update_date_value
      ...
 GROUP BY n.nid, ctu.field_update_date_value

I altered the example to use a fake column in order to highlight how the GROUP BY needs to be defined. Any column you reference without wrapping in an aggregate function should to be mentioned in the GROUP BY. I say should because MySQL is the only db I'm aware of that supports a GROUP BY where you can selectively omit columns - there are numerous SO questions about why queries work on MySQL but can't be ported without change to other dbs. Apparently in your case, you still need to define at least one.