use SUM on foreign keys where it matches rows id?

Question

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.

Answer 1

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.