How to sum two table counts in same query?-CodePudding

I have three tables in my postgesql db.

Cities: id, Name
Workers: id, city_id, name
Suppliers: id, city_id, name

I want to create an sql query to get sum of two tables in same city like:

City    w_count     s_count
A       5               2
B       8               4
C       9               7
E       9               0
E       0               1

sql is

select c.name, w.total, s.total
from (select city_id, count(*) as total from workers ) w
left join (select city_id, count(*) as total from suppliers) s
on w.city_id = s.city_id,
city as c
where c.id = w.city_id and c.id = s.city_id

If there is no suppliers in any city and there are workers in a city, the row is missing. But the cell should be 0.

How can I do this query?

CodePudding user response：

You should be joining from the city table to the 2 count subqueries:

SELECT c.Name AS City,
       COALESCE(w.total, 0) AS w_count,
       COALESCE(s.total, 0) AS s_count
FROM city c
LEFT JOIN
(
    SELECT city_id, COUNT(*) AS total
    FROM workers
    GROUP BY city_id
) w
    ON w.city_id = c.id
LEFT JOIN
(
    SELECT city_id, COUNT(*) AS total
    FROM suppliers
    GROUP BY city_id
) s
    ON s.city_id = c.id;