Calculating point layer values within polygon features in QGIS 2
With a bit of luck and suggestions from @Kazuhito, I ended up with
SELECT ST_UNION(B.geometry), B."Are_Number", SUM(D."EWjeAdr")
FROM "BereichBerechnung" AS B
JOIN "EW_Data" AS D ON contains(B.geometry, D.geometry)
GROUP BY B."Are_Number"
In case if there is a necessity to preserve geometries for which there are no overlaps between polygons and points in other words contains(B.geometry, D.geometry) command gives NULL use LEFT JOIN which will do the trick.
SELECT ST_UNION(B.geometry), B."Are_Number", SUM(D."EWjeAdr")
FROM "BereichBerechnung" AS B
LEFT JOIN "EW_Data" AS D ON contains(B.geometry, D.geometry)
GROUP BY B."Are_Number"
References:
- PostGIS view has missing features
- SpatiaLite SQL functions reference list
For QGIS 3
In the Processing Toolbox use the tool "Join attributes by location (summary)", make sure it's the summary tool and not the other one.
For QGIS 2
In the Processing Toolbox use the tool "Join attributes by location" and change the Attribute summary option to Take summary of intersecting features.
The Input layer should be the polygon and the Join Layer should be the points.
I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"), see the result of the joined polygon here:
