GROUP BY fields that are selected for grouping [#698604]

Hi,

Since there is no literature yet on this feature I wonder how do I choose fields for grouping. It says by the check-box "In All fields that are selected for grouping will be collapsed to one record per distinct value." Thats cool and I want that, but where do I choose grouping field?

I have 2 fields - name (text) and amount (number), both coming from cck 6.x-3.x-dev Multigroups (actual data below). Both have gear icon. I left default "Group results togather" for name and SUM for amount. It does not work, list is not changed.

Anna  	 5.00
Anna 	2.00
Victor 	0.50
Victor 	10.00

i want

Anna  	 7.00
Victor 	10.50

Comments

Comment #1

merlinofchaos commented 29 January 2010 at 02:37

Status:

Active

» Fixed

Once you turn on grouping, to the right of the field there is a gear icon. Click that, and select SUM on the numeric field.

Note that there is a CCK patch linked in the release notes -- you'll need that to be able to successfully use this on CCK fields.

Comment #2

henrijs.seso commented 29 January 2010 at 15:09

Category:	support	» bug
Status:	Fixed	» Active

Sorry to say it seems there is a bug. This is query views made and it does not work.

SELECT node.title AS node_title,
SUM(node_data_field_text.field_text_value) AS node_data_field_text_field_text_value,
node_data_field_text.field_text_value AS node_data_field_text_field_text_value_1
FROM node node 
LEFT JOIN content_type_kkk node_data_field_text ON node.vid = node_data_field_text.vid
WHERE node.type in ('', '', '', 'kkk')
GROUP BY node_title, node_data_field_text_field_text_value_1

SOLUTION is to remove ", node_data_field_text_field_text_value_1" from last line, then it works (tested in phpmyadmin, pasted query there).

My view

$view = new view;
$view->name = 'test1';
$view->description = 'test1';
$view->tag = 'test';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'field_recipe_ingredient_amount_value' => 'field_recipe_ingredient_amount_value',
  'field_recipe_ingredient_name_value' => 'field_recipe_ingredient_name_value',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'field_recipe_ingredient_amount_value' => array(
    'sortable' => 0,
    'align' => '',
    'separator' => '',
  ),
  'field_recipe_ingredient_name_value' => array(
    'align' => '',
    'separator' => '',
  ),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 0;
/* Field: Content: text (field_text) */
$handler->display->display_options['fields']['field_text_value']['id'] = 'field_text_value';
$handler->display->display_options['fields']['field_text_value']['table'] = 'node_data_field_text';
$handler->display->display_options['fields']['field_text_value']['field'] = 'field_text_value';
$handler->display->display_options['fields']['field_text_value']['group_type'] = 'sum';
$handler->display->display_options['fields']['field_text_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_text_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_text_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_text_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_text_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_text_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_text_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_text_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_text_value']['empty_zero'] = 0;
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'min' => '',
  'max' => '',
  'value' => '',
  'kkk' => 'kkk',
);

Comment #3

dawehner

German

commented 29 January 2010 at 15:12

Did you applied the groupby-patch of cck?

Comment #4

henrijs.seso commented 30 January 2010 at 15:25

No, just installed 3-dev version. There is a pach? Ill look for it.

Comment #5

dawehner

German

commented 30 January 2010 at 15:40

Ok, you don't read the release document: http://drupal.org/node/695302

Comment #6

henrijs.seso commented 30 January 2010 at 16:20

Found patch here... http://drupal.org/node/695298. Reading release document...

Comment #7

henrijs.seso commented 30 January 2010 at 17:40

My bad, but still same problem.

I applied this patch http://drupal.org/node/670344 and other http://drupal.org/node/695298 was already committed in cck 3.x-dev.

Views query I get still has same problem - 2nd argument after GROUP BY should not be there...

Does not work:

SELECT node_data_field_texttest.field_texttest_value AS node_data_field_texttest_field_texttest_value,
SUM(node_data_field_texttest.field_decimal_value) AS node_data_field_texttest_field_decimal_value,
node_data_field_texttest.field_decimal_value AS node_data_field_texttest_field_decimal_value_1
 FROM node node 
 LEFT JOIN content_type_kkk node_data_field_texttest ON node.vid = node_data_field_texttest.vid
 WHERE node.type in ('kkk')
 GROUP BY node_data_field_texttest_field_texttest_value, node_data_field_texttest_field_decimal_value_1

Works:

SELECT node_data_field_texttest.field_texttest_value AS node_data_field_texttest_field_texttest_value,
SUM(node_data_field_texttest.field_decimal_value) AS node_data_field_texttest_field_decimal_value,
node_data_field_texttest.field_decimal_value AS node_data_field_texttest_field_decimal_value_1
 FROM node node 
 LEFT JOIN content_type_kkk node_data_field_texttest ON node.vid = node_data_field_texttest.vid
 WHERE node.type in ('kkk')
 GROUP BY node_data_field_texttest_field_texttest_value

Comment #8

thinkling commented 3 July 2010 at 19:03

I'm having similar problems, I think. As soon as I add one aggregated field to my view, all the fields currently displayed get included in the GROUP BY clause of the query.

So for example, if I'm trying to tally how many posts each user has, I was pulling (Node:nid, User:name, Node:title) then turn on Grouping and change the nid field to be a COUNT, then I get:

SELECT COUNT(node.nid) AS nid 
(...)
GROUP BY users_name, users_uid, node_title

How do I group only by users_name or users_uid but not by node_title?

(Edit: I have CCK 2.7 and the above-mentioned CCK patch for groupby installed.)

Comment #9

merlinofchaos commented 3 July 2010 at 20:05

Status:

Active

» Closed (won't fix)

How do I group only by users_name or users_uid but not by node_title?

You cannot. ANSI SQL rules require that if GROUP BY is used, then all fields in the SELECT portion of the query must either be 1) aggregate functions or 2) part of the GROUP BY.

Comment #10

un11imig commented 31 October 2010 at 19:47

I have the same problem that #7. Any clue?

Comment #11

un11imig commented 1 November 2010 at 11:48

I solved, as is stated here: http://drupal.org/node/903768

using

function mysitemodifications_views_query_alter(&$view, &$query) {
if ($view->name == 'view_name') {
unset($query->fields['node_data_field_field_name_name_name_1']);
}
}

on a custom module I use to define custom modification on my website.

I understand that it is a temporal solution while this bug is solved.

Regards,

GROUP BY fields that are selected for grouping