Creating a nested sub-queery with three tables-CodePudding

I have 3 tables as mentioned bellow. Each section has two scans associated with it. Firstly in need to find the sections that are with in each neighborhood which I have done with the code below. Then I need to find the total capacity from scans (sum(cap_a cap_b)) for each section per neighborhood. What want is a selection that lists the neighborhoods and the total capacity of the sections in the neighborhood.

I have been looking at subqueries but I can't figure out the syntax for this problem.
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-subquery/

neighborhood (id,name,geom) 
sections (section,geom
scans (section,cap_a,cap_b)

SELECT neighborhood.name, sections.section
FROM neighborhood,sections
WHERE ST_WITHIN(section.geom,neighborhood.geom)

SELECT section,SUM(cap_a cap_b) AS capacity
        FROM scans

I have made this query so far but it is not giving the correct result I want.

SELECT
    neighborhood.id,
    neighborhood .name,
    capcity_of_neighborhood
FROM    
    neighborhood,
    section,
    scan
JOIN (
        SELECT section,SUM(cap_a cap_b) AS capacity
        FROM scans
        GROUP BY section
    ) AS capcity_of_neighborhood 
ON capcity_of_neighborhood.section = scans.section
WHERE 
    ST_WITHIN(section.geom,neighborhood.geom)
GROUP BY 1,2,3
;

CodePudding user response：

Hope I understand your query correctly. Try this:

  SELECT ne.id
       , ne.name,
       , sc.capacity_total 
    FROM neighborhood ne
    JOIN section se
      ON ST_WITHIN(se.geom, ne.geom)
    JOIN (
            SELECT section
                 , SUM(cap_a cap_b) AS capacity_total
              FROM scans
             GROUP 
                BY section
         ) AS sc
      ON sc.section = se.section
    ;