Show count of same attribute in feature (on visible map or by same field)

Question

In QGIS 2.14 I have a feature with two fields; Element Type (TF1, TF2, TF3, TF4) and Trail Nº (1, 2, 3, 4, 5, 6)

Each Trail has multiple Elements. I.E. Trail 1 may have 6* TF1 and 2* TF2, while Trail 2 may have 0* TF1 and 2* TF2

I am trying to report on the number of Elements on each individual trail with a feature count.

At present in a print composer I can show what Elements are on the trail through an attribute table. I am doing this by Categorising the feature by Trail Nº and then having layer settings showing only one trail (i.e. trail 3). This allows me to show a map with only a single trail and its elements. I am then using an attribute table with the "Show features only visible within map" turned on to show a full list of attributes on that map / trail.

This shows a list like Element Type TF1 TF3 TF3 TF3 TF9 TF9

I am also using the "remove duplicate rows from table" to show like this TF1 TF3 TF9

This is great but what I would like it to do is display the elements and the number of elements on that trail, I.E.

TF1 - 1, TF3 - 3, TF9 - 2

It would be easier if I was only trying to count the number of elements in the feature, but because I am trying to count the number of elements on each specific trail I am having trouble. I am happy for this to be either calculated and added to the feature by way of new column or calculated in an attribute table on the composer. Either is fine. Most important I don't want elements that are not on the trail showing in the list - I.E I don't want '0' counts to show.

some trails will have up to 70 of the same element so difficult to do this manually.

Have looked at Group Stats but not familiar enough with how to write expressions to make all this work.....

Answer 1

You can use virtual layer for creating a new calculated table. I'm not sure I exactly understand the result you want, but, if you want a table like :

Trail | Type | count

you can use a query like :

SELECT Trail, Element_type,  count(*) AS count
FROM layer 
GROUP BY Trail, Element_type 
ORDER BY Trail, Element_type

For a unique trail (to repeat as different virtual layers for each trail) :

SELECT Element_type,  count(*) AS count
FROM layer 
WHERE Trail = 1
GROUP BY Element_type 
ORDER BY Element_type

You can then use "Show features only visible within map" in the composer.

Note : To filter a layer you can use the query builder in your layer proprieties instead of categorizing. You can then categorize with Element type and use the legend for displaying the count of each type. (If this solution fit your needs, it is more simple).