Calculating the area of an intersection in PostGIS

Question

I've got two tables in a PostgreSQL/PostGIS database, blue and purple, both holding polygons:

Now I need to calculate the overlapping area, for each polygon of the blue grid with only one of the purple polygons (the one in yellow):

For that, I've built the following query

SELECT b.name, 
       SUM (ST_AREA (
            ST_INTERSECTION (
                b.geom,
                (SELECT geom FROM purple WHERE id = 'A') 
            )
        )
    )
FROM blue b LEFT JOIN purple p
ON ST_INTERSECTS (b.geom, p.geom)
GROUP BY b.name, b.geom
;

However, the calculation is incorrect in some cases (i.e. producing figures that exceed the total area of the squares in the grid). What's wrong with the query?

Answer 1

If 'A' is realy unique id

SELECT b.name, ST_AREA ( ST_INTERSECTION ( b.geom, p.geom ) )
FROM blue b, purple p
WHERE p.id = 'A';

Answer 2

As it is written, the query considers any polygon from the purple layer, regardless of its ID. Then, since you don't use the joined polygon but rather re-query the layer and fetch the A polygon, the area of intersection with the unrelated A polygon is computed.

For example, the lower left blue polygon is touching 3 purple polygons, so the area computation will be done (and summed) three time, each time considering the area of intersection with the A polygon.

You would need to filter by ID on the join condition and use the joined geometry:

SELECT b.name, 
       SUM (ST_AREA (
            ST_INTERSECTION (
                b.geom,
                p.geom
            )
        )
    )
FROM blue b LEFT JOIN purple p
ON ST_INTERSECTS (b.geom, p.geom) 
   AND p.ID = 'A'
GROUP BY b.name, b.geom;

PS: but as pointed by @RainForest, the sum/group by seems unnecessary as the intersection and area work with eventual multiparts