CODEWITHSUNDEEP
postgresqlselectsum
krltos
krltos

Reputation: 335

sum of values from 3 tables

I have 3 tables:

table A: building_id, region_code
table B: building_id, storey_no
table C: building_id, storey_no, room_no

What query would calculate sum of room numbers for all the buildings in each region with number of storeys in certain range (below is the sample query which doesn't work as I don't know how to include table B in it with the condition on number of storeys, etc.)?

SELECT SUM(C.room_no) AS room_sum, A.building_id 
FROM A,B,C
WHERE A.region_code=1 
AND A.building_id=C.building_id 
AND B.storey_no>3
AND B.storey_no<10
GROUP BY C.building_id,C.room_no

Upvotes: 0

Views: 76

Answers (1)

SMA
SMA

Reputation: 37073

Try joining on building_id from all three tables as below:

SELECT SUM(C.room_no) AS room_sum, C.building_id
FROM A INNER JOIN B
ON A.building_id = B.building_id
INNER JOIN C
ON A.building_id = C.building_id
WHERE A.region_code=1
AND B.storey_no>3
AND B.storey_no<10
GROUP BY C.building_id

Upvotes: 1

Related Questions