Hfrhyu

I simply have

• a polygon layer BereichBerechnung with a field "Are_Number"
  


• a point layer EW2017 with a field "EWjeAdr"
  

In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr" for points that are within each sub-polygon from the layer BereichBerechnung.

I have found a related thread on this topic: STIntersects - Update field to give me count of points in polygon. Nevertheless, cannot figure it out how to adjust my expression properly, any suggestions?

SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON BereichBerechnung.ogr_geometry.STContains(EW2017.ogr_geometry) = 1;
GROUP BY Are_Number

I am getting the following error

Any other smart suggestions/hints on how to aggregate values of a point layer based on polygons, e.g. IDs, should exclude application of Select Within plugin, Select by Location, Select Feature(s), Join attributes by location (summary) etc.

For QGIS 3.4

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.18

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:

With a bit of luck and suggestions from @Kazuhito, I ended up with

SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON contains(BereichBerechnung.geometry, EW_Data.geometry)
GROUP BY Are_Number

References:

• QGIS Documentation | Supported language


• SpatiaLite SQL functions reference list