How to perform SQL query using .STContains in MS SQL Server

Question

I have a table that stores polygons and a table that stores points with a value field. Both sets of data are stored using the Geometry type in MS SQL Server.

I have a simple requirement - I need to retrieve the records of points that are contained within the polygons and then sum the result based on a value field in the points table. So, the end result of the query is two columns: - polygon names - sum of the value field in the point table

DECLARE @area GEOMETRY  = (SELECT Shape FROM GREENFIELDAREAS WHERE AREANAME = 'Aerodrome')
SELECT NETDWELLINGS, Shape FROM ORA_NETDWELLINGCOMPLETIONS WHERE @area.STIntersects(Shape) = 1

The above query returns point geometries but only for 1 polygon (Aerodrome). What I need is to sum NETDWELLINGS and return that value for all the polygons.

Answer 1

SELECT g.id , SUM(o.netdwellings) as sum from g , o  WHERE g.geom.STIntersects(o.geom) = 1 group by g.id 

That would return sum of all o.geoms that intersect with g.geom. (did i understood question wrong ?)