All we need is an easy explanation of the problem, so here it is.
I have this table:
CREATE TABLE buildings ( id SERIAL PRIMARY KEY UNIQUE, name character varying(20) NOT NULL UNIQUE, document jsonb );
then this view:
CREATE VIEW v_rooms AS SELECT buildings.id AS building_id, buildings.name AS building_name, ((jsonb_array_elements(jsonb_array_elements(buildings.document -> 'levels'::text) -> 'rooms'::text) -> 'meta'::text) ->> 'sqft'::text)::numeric AS sqft FROM buildings GROUP BY buildings.id;
finally this view:
CREATE VIEW v_buildings AS SELECT buildings.id AS building_id, (buildings.document -> 'meta'::text) ->> 'name'::text AS name, sum(v_rooms.sqft) AS sqft FROM buildings, v_rooms GROUP BY buildings.id ORDER BY buildings.id;
All is well, except ‘sqft’ on v_buildings is returning the SUM of all the rows in v_rooms. I want to calculate SUM() where v_building.building_id == v_rooms.building_id.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Join to the aggregate, don’t aggregate the join:
SELECT b.id AS building_id, (b.document -> 'meta'::text) ->> 'name'::text AS name, r.sqft FROM buildings b JOIN ( SELECT building_id, sum(sqft) as sqft FROM v_rooms GROUP BY building_id ) r ON r.building_id = b.id;
Unrelated, but: although Postgres allows it, it’s usually a bad idea to include an
ORDER BY in a view definition.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂