SQL Query error: ambiguous column name geometry

Question

When I try a simple query

SELECT "Water_Service_Area_Boundaries_Non_Cadastral"."Network",  count(*), sum(st_length(geometry))
FROM "W_Mains_DSC_ExclAbandoned", "Water_Service_Area_Boundaries_Non_Cadastral"
WHERE ST_Intersects("Water_Service_Area_Boundaries_Non_Cadastral", "W_Mains_DSC_ExclAbandoned")
GROUP BY "Water_Service_Area_Boundaries_Non_Cadastral"."Network"

I get this error

Query preparation error on PRAGMA table_info(_tview): ambiguous column name: geometry

When I try it without the st_length it works to create a simple group with counts.

Also just the following works fine

 Select sum(st_length(geometry)), count(*)
 From  W_Mains_DSC_ExclAbandoned

Examples of the subset of the query that work independently but not when strung together

Just the total length

Just the group

When I simply add the st_length

I have tried with .geometry in the ST_Intersects and this gives correct counts but gives the pragma geometry error when the sum is added

Answer 1

In my opinion you do not need any SRID because you are not working with ST_Length_Spheroid().

What @she_weeds is pointing on is indeed correct.

SELECT ws."Network", count(ws."Network"), sum(st_length(ws.geometry))
FROM "Water_Service_Area_Boundaries_Non_Cadastral" AS ws, "W_Mains_DSC_ExclAbandoned" AS wmain
WHERE ST_Intersects(ws.geometry, wmain.geometry)
GROUP BY ws."Network"