I have the following schema (simplified):
Id INT, StaticLocation GEOGRAPHY
LocationId INT SpareCapacity INT
I'm trying to find, for each location a sum of the SpareCapacity of all locations within
x miles of it.
This is complicated by the fact that there are a few CTEs which I'm joining to but the principal is the same (I hope). I'm confused because this works as a standalone query:
SELECT sum(SpareCapacity) FROM Capacities UC JOIN Locations L ON L.Id = UC.LocationId WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10 GROUP BY u.StaticLocation
When I try to join it to a previous CTE though, I get the error:
Column 'Locations.StaticLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I add the column to the GROUP BY clause I get another error:
The type "geography" is not comparable. It cannot be used in the GROUP BY clause.
The query inside my CTE looks like:
SELECT k.LocationId, ( SELECT sum(SpareCapacity) Capacities FROM Capacities UC JOIN Location u ON u.id = UC.LocationId WHERE u.StaticLocation.STDistance(uk.StaticLocation) / 1609.344 < 10 --GROUP BY u.StaticLocation ) Vacancies FROM Cte_SomeData k -- cte which has some simple columns JOIN Locations uk on k.LocationId = uk.Id GROUP BY k.LocationId -- tried adding StaticLocation here without luck
If I replace
geography::Point(53, -2, 4326) it works (but obviously I need it relative to the Location's geography not a static point. Am I missing a group by somewhere?