How to create a view with group by node title and no grouping on nid

Question

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->name = 'duplicated_page_titles';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Duplicated Page Titles';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Duplicated Page Titles';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$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(
  'title' => 'title',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'title' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Field: COUNT(Content: Nid) */
$handler->display->display_options['fields']['nid']['id'] = 'nid';
$handler->display->display_options['fields']['nid']['table'] = 'node';
$handler->display->display_options['fields']['nid']['field'] = 'nid';
$handler->display->display_options['fields']['nid']['group_type'] = 'count';
/* Field: Content: 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']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
/* Filter criterion: Content: 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(
  'page' => 'page',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'duplicated-page-titles';

Answer 1

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.

Answer 2

hope this will help, click on Query settings:Settings

write the below code on your module..

function mymodule_views_query_alter(&$view, &$query) 
{
if ($view->name == 'your_view_name') 
{
  //dpm($query, __FUNCTION__);
  // dpm($view, __FUNCTION__);
   $query->add_groupby('node_title');
}
}

best of luck.