# 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 🙂