Count values from another layer based on value of current feature

Question

I have two layers. One is a shapefile of U.S. states where each state is labeled with the attribute STATE_NAME, and the other is a CSV data layer named rid with many points that are labeled with a State attribute. In this CSV data layer, there are many points for each state.

I want to create a column in the shapefile that contains the count of points per state.

Using the field calculator in the shapefile, I've attempted

aggregate(layer:= 'rid',
          aggregate:= 'count',
          expression:='State',
          filter:=attribute($currentfeature, 'STATE_NAME'))

but this results in 0 for each state. If I attempt without the filter, I get the total number of points regardless of state, resulting in the same number for each state.

Is there a filter that will allow me to count the number of points per state?

Answer 1

What I ended up doing:

1. Layer > Create Layer > New Virtual Layer…
2. Import rid CSV layer. Set query as select "State", count(*) AS “TERPS” from rid group by "State".
3. Click 'Add'. This creates a virtual layer that counts the number of points per date. Close dialog.
4. Go to the properties of the shapefile layer. Create a new join that joins to the virtual layer on "State", with the target field being STATE_NAME. This moves that data from the virtual layer over to the shapefile.