In the last few weeks I tried to make the "group by" clause work properly, but after applying patches and upgrades I'm still unable to build the proper query by UI.
For what I understand what is not working in the sql is that the field on which I'm doing the SUM is also in the GROUP BY clause so equal numbers are summed but different are not.

So now I'm trying to modify this last bit by hand. I was hoping that by intercepting $query with hook_views_query_alter() I could be able to take the GROUP BY clause, strip out the unnecessary field, and let it go back into the stream.

The problem is that I can't see how to do that, the $query object I find has no Group By clause. Where should I look?

#13 views-enable_groupby_in_query-1270064-13.patch623 bytesvalderama
PASSED: [[SimpleTest]]: [MySQL] 1,603 pass(es). View
#9 views-enable_groupby_in_query-1270064-9.patch633 bytesblisteringherb
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-enable_groupby_in_query-1270064-9.patch. Unable to apply patch. See the log in the details link for more information. View
#8 1270064_views_plugin_query_default_1.patch1.12 KBGribnif
FAILED: [[SimpleTest]]: [MySQL] 561 pass(es), 47 fail(s), and 70 exception(s). View
#7 1270064_views_plugin_query_default.patch1.35 KBGribnif
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1270064_views_plugin_query_default.patch. Unable to apply patch. See the log in the details link for more information. View
#2 querydump.png76.45 KBkeyiyek
Members fund testing for the Drupal project. Drupal Association Learn more


merlinofchaos’s picture

Status: Active » Fixed

Maybe you're experiencing the issue where CCK has not yet upgraded to be able to support GROUP BY properly? Assuming "the field" you refer to is a CCK field, that is. See #695298: Allow CCK to work with GROUP BY support in Views

And I don't know why the query object wouldn't have a group by clause. It should.

keyiyek’s picture

76.45 KB

Thanks for the reply.
I applied your patch from the linked post (the second @ #8) to cck 2.8, 2.9 and also. But I couldn't manage to make it work properly in any case (I'm the poster of #17). You can read another part of the story at

Now, back to the issue in the title the query as shown in the views preview is

SELECT node_data_field_tipo.field_tipo_nid
  AS node_data_field_tipo_field_tipo_nid,
  MAX(node.nid) AS nid, node_data_field_luogo.field_luogo_nid AS node_data_field_luogo_field_luogo_nid,
  SUM(node_data_field_ball_num.field_ball_num_value) AS node_data_field_ball_num_field_ball_num_value, node_data_field_ball_num.field_ball_num_value AS node_data_field_ball_num_field_ball_num_value_1
FROM node node
  LEFT JOIN content_field_tipo node_data_field_tipo ON node.vid = node_data_field_tipo.vid
  LEFT JOIN content_type_ingresso node_data_field_luogo ON node.vid = node_data_field_luogo.vid
  LEFT JOIN content_field_ball_num node_data_field_ball_num ON node.vid = node_data_field_ball_num.vid
  WHERE node.type in ('ingresso')
  GROUP BY node_data_field_tipo_field_tipo_nid, node_data_field_luogo_field_luogo_nid, node_data_field_ball_num_field_ball_num_value_1

from my little understanding of sql that last line should not be there.
so I call hook_views_query_alter() and in the attachment you can find the dmp($query)

I don't understand what I should modify, I was expecting something referring to the last couple of lines of the query quoted above.
Any suggestion?

btw since my question is about modifying sql manually I don't think that suggesting a workaround with the ui qualify for "fixing" the problem

keyiyek’s picture

Status: Fixed » Active
dawehner’s picture

A question, might be silly, but did you cleared the cache before re-running the view?

You would have to clear the views cache on admin/build/views/tools.

keyiyek’s picture

already done. as I wrote here the problem seems a duplication of the field on which I make the sum. Trying to figure out why

bohz’s picture

I have a very similar issue after updating 6.x-3.x-dev from the Feb 25 version (datestamp: 1298620633).
With previous version i built a view returning the following query:

SELECT DISTINCT(node.nid) AS nid,
node_node.type AS node_node_type,
node_node.nid AS node_node_nid,
node_node.vid AS node_node_vid
 FROM node node 
 LEFT JOIN content_field_rif_sezione node2 ON node.nid = node2.field_rif_sezione_nid
 LEFT JOIN node node_node ON node2.nid = node_node.nid
 LEFT JOIN term_node term_node_value_0 ON node_node.vid = term_node_value_0.vid AND term_node_value_0.tid = 888
 WHERE (node.status = 1) AND (node.type in ('lab')) AND (node.language in ('***CURRENT_LANGUAGE***')) AND (term_node_value_0.tid = 888)

With the current version, the last line GROUP BY nid is gone and the view displays duplicates.

Please note that I did not use the 'Group by' feature at all.

I also noticed an API version change, from 3.0-alpha1 to 3.0.

I am also interested in modifying the query in order to "restore" the GROUP BY nid line.

Any hint is appreciated,

Gribnif’s picture

1.35 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1270064_views_plugin_query_default.patch. Unable to apply patch. See the log in the details link for more information. View

This seems to be the same as #1284982: no "group by" clause being added for custom views field handler. It refers to the D7 version but is, I believe, the same bug.

Here is my attempt at a patch against 6.x-3.0-alpha4. It seems to work for me, in the case where a view handler is adding a GROUP BY and the UI does not have one. I believe it will also fix the case where just the UI has one.

Gribnif’s picture

1.12 KB
FAILED: [[SimpleTest]]: [MySQL] 561 pass(es), 47 fail(s), and 70 exception(s). View

I have revised my patch, since the previous version could produce an empty GROUP BY clause when DISTINCT was present in the query.

blisteringherb’s picture

Version: 6.x-3.x-dev » 6.x-3.0-rc3
633 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-enable_groupby_in_query-1270064-9.patch. Unable to apply patch. See the log in the details link for more information. View

I applied the last patch, and still ran into the empty GROUP BY clause when DISTINCT is present in the query. This patch is against 6.x-3.0-rc3.

markdorison’s picture

Status: Active » Needs review
alex.designworks’s picture

Title: Modify SQL of a view (GROUP BY) » Modify SQL of a view (GROUP BY) through UI and programmatically

To summarize, altering query to add a groupby should be done in hook_views_query_alter and it will work only if patch from #9 is applied. This has been tested on 7.x-3.1.

function MODULENAME_views_query_alter	(&$view, &$query)	{
  // Filter view by name.
  if ($view->name == 'your_view_name'){
    // Use add_groupby('field_name_as_appears_in_query')
Kars-T’s picture

Status: Needs review » Fixed

Dear fellow Drupal enthusiasts,

this issue is now lasting for a very long time in the issue queue and was unfortunately never solved. As Drupal is a open source project everyone is helping on voluntary basis. So that this is was not solved is nothing personal and means no harm. But perhaps no one had time to deal with this issue, maybe it is too complex or did not pose the problem comprehensible.

But this issue is not the only one. There are thousands of issues on that have never been worked on or could not be processed. This means that we are building a wave that is unmanageable and just a problem for the Drupal project as a whole. Please help us keep the issue queue smaller and more manageable.

Please read again, "Making an issue report" and see if you can improve the issue. Test the problem with the current Core and modules. Maybe the problem doesn't exist anymore, is a duplicate or has even been solved within this issue but never closed.

Help can also be found for it on IRC and in the user groups.

In order this issue no longer remains, I put this issue to "fixed".

If there is new information, please re-open the issue.

valderama’s picture

Status: Fixed » Needs review
623 bytes
PASSED: [[SimpleTest]]: [MySQL] 1,603 pass(es). View

Dear Karsten,

this Issue is still valid - at least in Drupal 7 with Views 7.x-3.5 (and also in dev).

I can confirm the patch provided in #9 is working. I fully verify comment #11. I was also trying to add a group-by in hook_query_alter but it did not work until I added the changes proposed in #9.

With the hope to fix this issue for the broader community as well I made a new patch against 7.x-3.x-dev.

Let finally fix this issue!

Thanks + best,

Status: Needs review » Needs work

The last submitted patch, views-enable_groupby_in_query-1270064-13.patch, failed testing.

valderama’s picture

Version: 6.x-3.0-rc3 » 7.x-3.x-dev
Status: Needs work » Needs review

changing version for the test bot

valderama’s picture

Kars-T’s picture

Category: support » feature

I set this to feature request than :)

dawehner’s picture

Issue tags: +Needs tests

If we really change any behavior, we need tests for existing and expected new behavior.

Prine’s picture

#13 patch then #11 implementation worked for me. Thanks!

awolfey’s picture

I want to bring your attention to #1615438: Search terms: nodes dont appear when using multiple arguments, which also deals with the same piece of code.