How to create a view with group by node title and no grouping on nid I want to create a view that shows nodes with duplicate titles. I am using Views 7.x-3.7 and Drupal 7.24

To do this I created a view with 2 fields: nid and title. 

I turned on Use aggregation and for the node title field set it to group results together, and for the nid field selected count.

The query generated was:

SELECT node.title AS node_title, node.nid AS nid, COUNT(node.nid) AS nid_1
FROM 
{node} node
GROUP BY node_title, nid
LIMIT 10 OFFSET 0


Clearly this won't work - we can't count nids if we are grouping by the nid.

How would I change my view so the query outputted was:

SELECT node.title AS node_title, COUNT(node.nid) AS nid_count
FROM 
{node} node
GROUP BY node_title
LIMIT 10 OFFSET 0


Is this even possible? And if not why not? I would ideally like to achieve this without hook_query_alter if possible.



Exported view:

$view = new view();
$view-&gt;name = 'duplicated_page_titles';
$view-&gt;description = '';
$view-&gt;tag = 'default';
$view-&gt;base_table = 'node';
$view-&gt;human_name = 'Duplicated Page Titles';
$view-&gt;core = 7;
$view-&gt;api_version = '3.0';
$view-&gt;disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view-&gt;new_display('default', 'Master', 'default');
$handler-&gt;display-&gt;display_options['title'] = 'Duplicated Page Titles';
$handler-&gt;display-&gt;display_options['use_more_always'] = FALSE;
$handler-&gt;display-&gt;display_options['group_by'] = TRUE;
$handler-&gt;display-&gt;display_options['access']['type'] = 'perm';
$handler-&gt;display-&gt;display_options['cache']['type'] = 'none';
$handler-&gt;display-&gt;display_options['query']['type'] = 'views_query';
$handler-&gt;display-&gt;display_options['exposed_form']['type'] = 'basic';
$handler-&gt;display-&gt;display_options['pager']['type'] = 'none';
$handler-&gt;display-&gt;display_options['pager']['options']['offset'] = '0';
$handler-&gt;display-&gt;display_options['style_plugin'] = 'table';
$handler-&gt;display-&gt;display_options['style_options']['columns'] = array(
  'title' =&gt; 'title',
);
$handler-&gt;display-&gt;display_options['style_options']['default'] = '-1';
$handler-&gt;display-&gt;display_options['style_options']['info'] = array(
  'title' =&gt; array(
    'sortable' =&gt; 0,
    'default_sort_order' =&gt; 'asc',
    'align' =&gt; '',
    'separator' =&gt; '',
    'empty_column' =&gt; 0,
  ),
);
/* Field: COUNT(Content: Nid) */
$handler-&gt;display-&gt;display_options['fields']['nid']['id'] = 'nid';
$handler-&gt;display-&gt;display_options['fields']['nid']['table'] = 'node';
$handler-&gt;display-&gt;display_options['fields']['nid']['field'] = 'nid';
$handler-&gt;display-&gt;display_options['fields']['nid']['group_type'] = 'count';
/* Field: Content: Title */
$handler-&gt;display-&gt;display_options['fields']['title']['id'] = 'title';
$handler-&gt;display-&gt;display_options['fields']['title']['table'] = 'node';
$handler-&gt;display-&gt;display_options['fields']['title']['field'] = 'title';
$handler-&gt;display-&gt;display_options['fields']['title']['label'] = '';
$handler-&gt;display-&gt;display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler-&gt;display-&gt;display_options['fields']['title']['alter']['ellipsis'] = FALSE;
/* Filter criterion: Content: Type */
$handler-&gt;display-&gt;display_options['filters']['type']['id'] = 'type';
$handler-&gt;display-&gt;display_options['filters']['type']['table'] = 'node';
$handler-&gt;display-&gt;display_options['filters']['type']['field'] = 'type';
$handler-&gt;display-&gt;display_options['filters']['type']['value'] = array(
  'page' =&gt; 'page',
);

/* Display: Page */
$handler = $view-&gt;new_display('page', 'Page', 'page');
$handler-&gt;display-&gt;display_options['path'] = 'duplicated-page-titles';
If you display nodetitle as a link it won't show as a duplicate. Uncheck 'display as a link' in fieldsettings of title and you should be good to go.

I imported your view, made this change, created a duplicate titled page and my view showed duplicate results immediately grouped by title, displaying the nodecount of that title.