use SUM on foreign keys where it matches rows id? 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 -&gt; 'levels'::text) -&gt; 'rooms'::text) -&gt; 'meta'::text) -&gt;&gt; '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 -&gt; 'meta'::text) -&gt;&gt; '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.
Join to the aggregate, don't aggregate the join:

SELECT b.id AS building_id,
      (b.document -&gt; 'meta'::text) -&gt;&gt; '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.