use SUM on foreign keys where it matches rows id?

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.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply