MySQL #1140 - Mixing of GROUP columns

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.

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.