Count subset of points in polygons

Question

Being an SQL-noob, I found a working solution to generate a virtual layer with counts of points in the polygons of another layer, inspired by this https://gis.stackexchange.com/a/54680

SELECT polygons.fid,polygons.geometry, count(points.geometry) AS counts
FROM polygons
LEFT JOIN points
ON st_contains(polygons.geometry,points.geometry )
GROUP BY polygons.fid;

This returns a new polygon layer with the fid and the count of points.

But the end result I would like to achieve is, to only count a subset of points, represented by

points.controlled IS NULL

I can't find out where and how to inject this criteria into the query. All trial-and-error only led to the latter.

There is already a similar question, but the answer misses the subset part completely
Counting points in a polygon for a subset of my points layer?

Answer 1

You can add the filter in the join condition:

SELECT polygons.fid,polygons.geometry, count(points.geometry) AS counts
FROM polygons
LEFT JOIN points
ON st_contains(polygons.geometry,points.geometry) and points.controlled IS NULL
GROUP BY polygons.fid;