Counting fire occurences per county per year using QGIS

Question

I wish to loop through this shapefile, and for each "COUNTY_NM", count the number of values corresponding to each "layer" value.

I want to output a CSV or equivalent which would be show the number of values per each county per year e.g. "Okanogan County, 2000:1, 2002:16, 2003:4" etc.

Answer 1

You can use the "Statistics by categories" from the Vector analysis toolbox in QGIS.

Select your layer, leave 'Field to calculate statistics on' empty, select "COUNTY_NM" and "layer" as your 'Field(s) with catagories'

Output:

Answer 2

There is a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume there is a layer called 'points' with its attribute table, see image below.

With the following query, it is possible to show the number of values per each county per year.

SELECT "COUNTY_NM" || ' | ' || GROUP_CONCAT("layer" || ' : ' || n) AS res
FROM (
    SELECT "COUNTY_NM", "layer", COUNT("layer") AS n
    FROM "points"
    GROUP BY "layer", "COUNTY_NM")
GROUP BY "COUNTY_NM"
ORDER BY "COUNTY_NM", "layer", n

The output attribute table will look like