How can I join multiple tables for count?

user_694716 0
Viewed 75

I have 3 tables named houses, trees, rivers. All of these tables have city_id column. I want to group total counts by cities. Cities are in another table. My database is postgresql.

city_name    trees    houses      rivers
City-1       1000     200         1
City-2       300      100         2
City-3       4000     210         4

I can get for trees

SELECT 
    city.name as city_name, 
    count(*) as trees
FROM trees as t, cities as city
WHERE t.city_id = city.city_id
GROUP BY city.name

But I could not join three tables in sama query.

postgresql sql
2 Answers

To avoid issues with duplication of rows in a JOIN it's probably easiest to do the aggregation in subqueries and then JOIN them:

SELECT c.name, 
       COALESCE(t.cnt, 0) AS trees,
       COALESCE(h.cnt, 0) AS houses,
       COALESCE(r.cnt, 0) AS rivers
FROM cities c
LEFT JOIN (SELECT city_id, COUNT(*) AS cnt
           FROM trees
           GROUP BY city_id) t ON t.city_id = c.city_id
LEFT JOIN (SELECT city_id, COUNT(*) AS cnt
           FROM houses
           GROUP BY city_id) h ON h.city_id = c.city_id
LEFT JOIN (SELECT city_id, COUNT(*) AS cnt
           FROM rivers
           GROUP BY city_id) r ON r.city_id = c.city_id

We use a LEFT JOIN in case a given city has no trees, houses or rivers.

Demo on dbfiddle

user_94737640

An alternative to Nick's answer:

SELECT 
    city.name as city_name, 
    count(distinct t.id) as trees,
    count(distinct h.id) as houses,
    count(distinct r.id) as rivers
FROM  cities as city
left join trees as t on t.city_id = city.city_id
left join rivers as r on r.city_id = city.city_id
left join houses as h on h.city_id = city.city_id
GROUP BY city.name

--

Not sure of the performance implications specifically with Postgres, but here's a (fairly old, so things might have moved on since) article suggesting count(distinct) can be slow in Postgres, together with options:

postgresql COUNT(DISTINCT ...) very slow

user_23852180
Related