Creating a View with Distinct Results

Question

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:

Blog
Blog
Blog
...
Book
Book
Book
Book
...
Web Article
Web Article
etc.

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?

Answer 1

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.