How to filter for distinct values and apply multiple filters in QGIS attribute table?

Question

I am very new to QGIS. I am doing an assignment for class where we have to answer several questions about data obtained from Global Fishing Watch. One of the questions is how many vessels have a tonnage greater than 1200? I know how to filter for this. However, the data includes multiple voyages for each ship. As you can see in the attached screenshot, when I filter for tonnage, many of the ships appear more than once (the MMSI field is the ID number of the ships). My question is how do I filter for tonnage AND make it so that each ship will only appear once? I figure I need to filter by tonnage and filter the MMSI for distinct values, but I am unsure how to apply two filters to a search and I need to know how I can filter a field so that it will remove duplicates. I have a feeling that the "count_distinct" function is what I need, but so far it hasn't worked, I think my syntax is incorrect. As for applying two filters, just putting "and" between my expressions has not worked thus far. As I said I just started and do not yet have a hang of how the expression syntax works.

Answer 1

You are on the right track with count_distinct.

First make sure Editing is disabled for the layer.

Then we'll add a filter to the entire layer by doing the following: right click layer > Properties > Source > Query Builder (bottom right)

For the expression write "tonnage" > 1200

Click OK and close the filter then open the Statistics tool:

In the Statistics tool select the layer from the drop down list and write the following in the expression box: count_distinct("mmsi")

This should give you a count of distinct mmsi values from your layer which has now been filtered to only tonnages above 1200.

Note: The filter does not delete any data, it just looks like that. What it does it tell QGIS to ignore everything outside the filter. After getting your count just go back into the filter and clear it.