At some point (rc2 upgrade?) the group by stopped working on my view. The sql generated has a group-by node_vid that essentially negates any grouping. I'm not sure what changed or how to get around this.

Here's the sql generated:
SELECT COUNT(node.nid) AS nid, node.type AS node_type, node_data_field_progtype.field_progtype_value AS node_data_field_progtype_field_progtype_value, node.vid AS node_vid FROM node node LEFT JOIN content_type_program node_data_field_progtype ON node.vid = node_data_field_progtype.vid WHERE (node.type in ('program')) AND (node.status <> 0) GROUP BY node_data_field_progtype_field_progtype_value, node_type, node_vid ORDER BY node_data_field_progtype_field_progtype_value DESC

If I remove by hand the group-by node_vid... the query works.

Fields to Group On = field_progtype
Sql Aggregation Function = count
Fields to Aggregate with the SQL Function = Node: NID
Field to Sort Resultset On: field_progtype

Tho limited to displaying one row, the "Funded Program Summary Statistics" section would like to display all 67... and not group by.

#6 views_groupby-651546-6.patch979 bytesglennpratt
Members fund testing for the Drupal project. Drupal Association Learn more


knystrom’s picture

We rolled back to rc1 and the issue disappeared. We see that 4 lines of code were commented out between the versions, tho we don't see documentation as to why.

Here's the sql generated by rc1:
SELECT COUNT(node.nid) AS nid, node_data_field_progtype.field_progtype_value AS node_data_field_progtype_field_progtype_value FROM node node LEFT JOIN content_type_program node_data_field_progtype ON node.vid = node_data_field_progtype.vid WHERE (node.type in ('program')) AND (node.status <> 0) GROUP BY node_data_field_progtype_field_progtype_value ORDER BY node_data_field_progtype_field_progtype_value DESC

johnbarclay’s picture

I don't fully understand the function query() in; but changing:

            // if ($key != $bfield) {
           //  unset($this->query->fields[$key]);
            // } 


           //  if ($key != $bfield) {
           //  } 

seems to deal with the vid problem. Perhaps when the $bfield line was commented out, too the unset line was commented out by accident.

    /** Add all other fields into count, to make sure they do not mess up group_by **/
    if (is_array($this->query->fields)) {
      foreach ($this->query->fields as $key => $field) {
        if ( !in_array($key, $to_group) &&
             !in_array($key, $to_aggregate) ) {

             if ($key != $bfield) {
           //  unset($this->query->fields[$key]);
        else if (in_array($key, $to_aggregate)) {
            $this->query->fields[$key][$sql_func] = TRUE;
            $this->query->fields[$key]['alias'] = $key;

neffets’s picture

Priority: Normal » Critical
Issue tags: +views aggregate

I think the problem is the new VIEWS version (6.x-2.8)

in views/includes/ line 977
$groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";

it adds an "non_aggregate" array to the group by fields, so there were added nid and vid too

To the non_aggregate array are in this query-function added all fields, that have not attribute
$field['count'] or attribute $field['aggregate']

A pathc with a aggregate attribute for all fields ($this->query->fields[fields]->aggregate='no') would break the views patch for ANSI SQL compliance (e.g. with postgres) see

But we can trick it if all results would be distinct, then a patch would do like:

--- views_groupby/handlers/ 2009-12-11 03:14:10.000000000 +0100
+++ views_groupby/handlers/ 2009-12-11 06:27:53.000000000 +0100
@@ -261,6 +261,10 @@
$sz_to_group .= $field;
+ if ($count) { // issue #385158: query->add_groupby & aggregate vs. nonaggregate fields und issue #651546: Group By not working due to vid
+ $this->query->distinct=TRUE;
+ $this->query->no_distinct='views_groupby'; //prevent views-behaviour to add nid(base_field) allways
+ }

$views_fields = $this->_get_views_fields($just_aliases=TRUE);

stefanhapper’s picture

Re comment #3 -> we are facing the same problem, but with Views 2.6 (not 2.8)

We tried with Views Group By dev versions of 6 November and 22 November 2009

Was anyone able to solve this problem? For us it makes Views Group By completely unusable for the moment.

Anonymous’s picture

Same problem, group by node_vid renders grouping obsolete. Using rc2 and views 6.x-2.7.

Reverted back to rc1 and it worked again...

glennpratt’s picture

979 bytes

#3 works for me. Here's a patch, credit to neffets.

mattiasj’s picture

Same problem here with RC2 and Views 2.8. Will try this patch out!

mineshaftgap’s picture

I think the patch in #6 is a MySQL only solution, as it breaks on Postgres.

glennpratt’s picture

mineshaftgap: Could your issue be related to this Views issue? #506818: [Postgres] GROUP BY uses field names instead of aliases

mineshaftgap’s picture


I think that you are correct. Thanks for the input, unfortunately it seems like it is still an open issue, so for now I will be using hook_views_query_alter/hook_views_pre_render/etc or jumping out of Views for my needs.

glennpratt’s picture

Status: Active » Needs review

Changing to needs review, hoping to get more eyes on this.

cpall’s picture

Patched worked beautifully for me. (mySQL)

denney’s picture

Patch worked perfect for me to.

TravisCarden’s picture

#6 works for me, too. (MySQL 5.0)

darrick’s picture

This patch worked well for me. However, I also had some trouble with having a date field set in the sort field which was populating the groupby array.

alexharries’s picture

Thank you Glenn - that worked perfectly for me too =o)

nottaken’s picture

#6 worked for me. I was banging my head trying to figure this out for a few hours. Thanks.

skylord’s picture

#6 works for me too. Please, make release with it!

arithmetric’s picture

Status: Needs review » Reviewed & tested by the community

This patch is working for me too. Marking it as RTBC.

Liutauras’s picture

#6 patch works for me too. Please, make release with it!

arithmetric’s picture

Version: 6.x-1.0-rc2 » 6.x-1.x-dev
Status: Reviewed & tested by the community » Fixed

This patch was committed with this change:

It's now part of the dev release (6.x-1.x).

Status: Fixed » Closed (fixed)
Issue tags: -views aggregate

Automatically closed -- issue fixed for 2 weeks with no activity.