I've created a view called 'Browse'. I've set the 'Query Settings' to show only DISTINCT (but it doesn't work).

Background: I have a 'Resource' content type with a field list field called 'type'. Types may include 'blog', 'book', 'web article', etc. (there are only 8 types). I have about 80 resources in this content type, each with a 'type'.

When I create my view with DISTINCT set and content type=resource and 'type' as the only field it displays about 15 instances of each type, so it ends up looking something like this:

Web Article
Web Article

I only want each 'type' to display once and link to the views I've already setup that show all the resource content types with that particular type set (ie. if a user clicks 'blog', which should only appear once, it takes them to a page view listing all the blogs in the db.

Does anyone know how to ensure my View shows only distinct values for items in this field_list?


It does not work because the rows are all unique to start of with. Switch on SQL preview in views settings, and you will see that it adds the node's nid to the result in the SQL. Now add nid to the fields, and you will see something like

Book, nid:5
Book, nid:7

Book, nid:5 is not equal to Book, nid:7. For distinct to work, whole rows need to be equal.

What you need is aggregation.

To do that, switch on aggregation for the view in the advanced pane. Then set aggregation to COUNT on the nid.

Now your result will look something like this:

Blog, nid:6 // ie 6 rows with field value blog
Book, nid:2 // ie 2 rows with field value book

Then, finally, to get the output you want, just hide the nid from the views display.

In essence, what you have done is to group by the field 'type', and to count how many nids (rows) there are in each group. And then hide the count as you don't need to display it.

| improve this answer | |
  • Thank you very much for the thorough explanation. Oddly I found that when I did this, because I'm also using an image field, I get a mysql error and no results show. But fixed it using this method: drupal.org/node/1809584 Basically: remove the image field and re-add it. Strange that. – Sage Feb 19 '15 at 7:41
  • Even stranger: it shows in the preview that each item only appears once but on the actual page it still shows each item multiple times even with distinct and aggregation and the settings you supplied. I tried different browsers and flushing the cache...and the preview is different than the actual page...never seen that before. – Sage Feb 19 '15 at 7:50
  • Ok, got it working - have to remove DISTINCT after doing these things. Thanks again :) – Sage Feb 19 '15 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.