Geography Inside a CTE I have the following schema (simplified):

Locations

Id INT,
StaticLocation GEOGRAPHY


Capacities

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 &lt; 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 &lt; 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 uk.StaticLocation with 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?
Using the examples from SQL Geography data type column in GROUP BY clause, I was able to group by a geography location.  See if this works for you.

--demo set up
Declare @Locations table (ID int, StaticLocation GEOGRAPHY)
Declare @Capacity table (LocationId int, SpareCapacity int)
insert into @Locations(id, StaticLocation) values
(1,geography::Point(41.878899,-87.636297, 4326)),
(2,geography::Point(40.749014,-73.985317, 4326))

insert into @Capacity(LocationId,SpareCapacity) values
(1,100),
(1,1000),
(2,200),
(2,2000)

--Solution
SELECT GEOGRAPHY::STGeomFromText(L.StaticLocation.STAsText(), 4326) as StaticLocation
    ,sum(SpareCapacity) as SumSpareCapacity
FROM @Capacity UC
JOIN @Locations L
    ON L.Id = UC.LocationId
--WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 &lt; 10   
GROUP BY L.StaticLocation.STAsText()




| StaticLocation                                 | SumSpareCapacity |
|------------------------------------------------|------------------|
| 0xE6100000010C821FD5B0DF5F444057CC086F0F7F52C0 | 2200             |
| 0xE6100000010C59BE2EC37FF04440BF620D17B9E855C0 | 1100             |