Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table cities (city_id int, name varchar(20))
insert into cities values 
(1, 'London'),
(2, 'New York')
2 rows affected

create table trees (city_id int);
create table houses (city_id int);
create table rivers (city_id int);
insert into trees values (1), (1);
insert into houses values (1), (1), (2), (2), (1);
insert into rivers values (1), (1), (1), (2);
2 rows affected

5 rows affected

4 rows affected

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


name
trees
houses
rivers


London
2
3
3

New York
0
2
1