How to set group by in drupal 7 views?????????

Comments

spovlot’s picture

Do you want a grouping field such as :

Type 1
    Row 1
    Row 2
Type 2
    Row 3
    Row 4

If so, use the Format Settings to select a Grouping Field.

If you want to use COUNT() or SUM() aggregation, change "Use Aggregation" under the Advanced settings.

Khetam’s picture

do the following:

  1. Edit your view
  2. Click on setting (format setting)
  3. Choose the field that you want to group the view
  4. Save your view

or check this link:
http://drupal.stackexchange.com/questions/4770/how-do-i-group-elements-i...

khuram’s picture

I am actually trying to do something same but I have a View in Drupal 7 of type Content, instead of fields. Views can be of types Content and fields; for fields type of View, these options are surely available but for View of type Content this option isn't available for me at least. I am trying to figure out how to add Grouping in this case, when this option isn't there as I am using View of type Content instead of fields. Just to clarify, when I modify my View to be of type fields then that option becomes visible, and as soon as I modify the node to be of type Content(like showing whole nodes) that option goes away. Trying to figure out a way to do Grouping in this case, any sort of help would be greatly appreciated.

Thanks, Khuram!

spovlot’s picture

You can group by a field but still show the Content node or teaser. To do this, use the Fields setting. Add a the field that you will be grouping on and exclude it from display. Then add Content: Rendered Node as a field. You can set this field's setting to Display = "Show complete entity" and View mode = "Full Content" or teaser. Then set the grouping field to the first field you added.

fhdrupal’s picture

How can I group contents by titles with the similar topics like Google news. any idea?

sushilck’s picture

function tasteusa_event_nodes_views_query_alter(&$view, &$query){
  if ($view->name == 'your_view_name')
  {
	$query->add_field('node', 'nid', 'node_nid', array('function' => 'groupby'));
        $query->add_groupby('node.nid');
	//dpm($query);
  }
graceman9’s picture

So much time has passed and still no real answer in the most of topics. For sure someone knows the truth, but they are keep silence :)

The last answer by @sushilck will produce GROUP BY for the every field in the SELECT query, so it's not an option as for me.

The better option that ever exists is to use hook_query_alter() instead of hook_views_query_alter().
But this hook a bit specific. We may add a tag to the views query to make sure that it will be fired.

1) Add a tag to the view (sure, you can also define it from the admin UI, it's example for the code-driven approach)

/**
 * Implements hook_views_default_views().
 */
function MODULENAME_views_default_views() {
  // .................... your views definition

  $handler->display->display_options['query']['options']['query_tags'] = array(
    0 => 'my_super_tag',
  );

  // ....................
}

2) Use hook_query_alter().

/**
 * Implements hook_query_alter().
 */
function MODULENAME_query_alter(QueryAlterableInterface $query) {
  if ($query->hasTag('my_super_tag')) {
    $query->groupBy('users.uid');
  }
}

But wait..... There are some explanation from guru https://www.drupal.org/node/1365688#comment-5344230:

You're wrong. The presence of GROUP BY according to ANSI sql requires all fields in the SELECT to either be aggregate or in the GROUP BY.

This is the intended behavior; otherwise, pgsql queries break using group by.

According to this explanation the previous comment by @sushilck is an option in fact.

SalvadorP’s picture

Works fine for me.

Thanks for this option.

jdesrig’s picture

@graceman9 This worked perfectly, thanks. Though, instead of actually creating a tag in code, it seemed that views automatically created one for me on a display basis. The tag I found was 'views_[view_display_name]'