Drupal - Why `GROUP BY` in hook_views_query_alter() doesn't work?

Use add_field() with array('function' => 'groupby') parameter.

$query->add_field('node', 'nid', 'node_nid', array('function' => 'groupby'));
$query->add_groupby('node.nid');

Aditional information:

After using add_groupby you might see the next code after GROUP BY:

GROUP BY node.nid, nid

See the next issue: https://drupal.org/node/1578808

To avoid unnecessary GROUP BY conditions add:

$query->distinct = TRUE;