Updating column with multiple sub-queries I have created the following query which is used to calculate a total from the results of a spatial query:

SELECT
  SUM(med_price)
FROM (SELECT
  ST_INTERSECTION(a.geom, (SELECT
    ST_SETSRID(geom, 4326)
  FROM catalogue_table
  WHERE id_number = '2211654')
  ) AS geom,
  small_price,
  med_price,
  large_price
FROM pricing_table AS a
WHERE ST_INTERSECTS(ST_SETSRID(a.geom, 4326), (SELECT
  ST_SETSRID(geom, 4326)
FROM catalogue_table
WHERE id_number = '2211654')
)) AS total_price


My tables look like this:

CREATE TABLE pricing_table
(
  gid serial NOT NULL,
  tile_id numeric,
  small_price numeric,
  med_price numeric,
  large_price numeric,
  geom geometry(MultiPolygon,4326)
)


And:

CREATE TABLE catalogue_table
(
  ogc_fid serial NOT NULL,
  geom geometry(Geometry,4326),
  id_number character varying,
  scale integer,
  scale_code character varying
)


I am trying to modify this query so that it calculates the total price for all rows, instead of just a single row with a specific id_number. However, whenever I try and modify my query to do this, I get: 

ERROR: more than one row returned by a subquery used as an expression. SQL state: 21000


I would like to create a 'price' column on the catalogue table, and populate this with calculated prices, which, depending on the 'scale_code' from the catalogue_table (either small, medium or large) will calculate a total from the relevant column in the 'pricing_table'.
The trick here is to not to get overwhelmed by the fact that there is a spatial datatype involved.  Then it becomes a fairly simple aggregate query over a join between the pricing table and the catalog table where the join just happens to be a spatial intersection.

SELECT c.id_number, SUM(med_price) 
FROM pricing_table p 
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326)) 
GROUP BY c.id_number


Then to incorporate the scaling factor into the sum you can use a case statement in a subquery, similar to the following

SELECT p.id_number, SUM(price)
FROM (
SELECT c.id_number, 
    CASE WHEN scaling_code = 'small' THEN small_price
        WHEN scale_code = 'medium' THEN med_price
        ELSE large_price
    END price
FROM pricing_table p 
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326)) 
    ) p
GROUP BY p.id_number