How to add DISTINCT, GROUP BY clause in drupal view

Question

I'm using drupal 6 and i've created a view and in view query i want to add

GROUP BY AND DISTINCT CLAUSE.

For that i tried to use views_groupby contributed module but it only provide COUNT SQL Aggregation Function.

I don't know how to add group by clause and distinct clause in view query using hook_views_query_alter(&$view, &$query).

So that it look like this -

SELECT DISTINCT(node.nid) AS nid, OTHER_COLUMN 
FROM TABLE_NAME 
JOIN ANOTHER_TABLE ON JOIN_CONDITION 
GROUP BY nid

Any help or suggestion would be greatly appreciated.

Solution 1

On your View edit form, under Basic Settings, try turning the Distinct setting on.

You may also try to use the Gear Icon for the Style section under Basic Settings. There you can specify a field to group by, if your row style is fields.

Solution 2

Group By is not working on hook_views_query_alter and it is known as drupal views module issue. which is reported at https://drupal.org/node/590534, http://drupal.org/node/1608742, http://drupal.org/node/389230#comment-2637200

Also views Aggregation Functions is not given desired result, because it will add all fields and sort order fields to group by criteria. So, You should use other hooks such as hook_query_alter.

function hook_query_alter( &$query) {

  if($query->alterMetaData['view']->name == "your_views_name"){
     $query->groupBy("domain_source.domain_id");
     //also you can unset other group by critera by using, unset($query->getGroupBy());
  }
}

Solution 3

In Drupal 8, you will add "Group by" that way:

use Drupal\Core\Database\Query\AlterableInterface;

function MYMODULE_query_alter(AlterableInterface $query) {
  if (isset($query->alterMetaData['view'])) {
    if($query->alterMetaData['view']->id() == 'replace_by_view_machine_name') {
      // Group by UID to remove duplicates from View results
      $query->groupBy('users_field_data.uid');
    }
  }
}