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) )
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'.