I have three tables in the database: cities, categories and organizations

cities

categories

orgs

I would like to get an overview with all cities and under each city each category should be displayed with the number of corresponding organizations.

**London**
doctor: 12
lawyer: 15
psychologist: 9

**Berlin**
doctor: 22
lawyer: 17
psychologist: 11

With the following query I output all cities:

SELECT name FROM cities

And with the following query I output categories and number of organizations:

SELECT 
c.name AS category_name, 
COUNT (o.id) AS org_number
FROM categories c LEFT JOIN orgs o ON c.id = o.cat_id
WHERE o.city_id = $placeId 
GROUP BY c.name 
ORDER BY c.name ASC

$placeId is is the id of the corresponding city

The data is output as desired.

The only thing I am missing is that the NULL values are not output.

Like this:

**New York**
doctor: 22
lawyer: **0**
psychologist: **0**

Thank you very much for your support.

CodePudding user response：

You can produce the whole combination of city/category with a cross join between those tables. Then, you can join that result with the organization table, as in:

select c.name as city, a.name as category, count(o.city_id) as cnt
from cities c
cross join categories a
left join orgs o on o.city_id = c.id and o.category_id = a.id
group by c.id, a.id
order by c.id, a.id

Result:

 city      category      cnt 
 --------- ------------- --- 
 london    doctor        0   
 london    lawyer        1   
 london    psychologist  0   
 new york  doctor        1   
 new york  lawyer        0   
 new york  psychologist  0   
 berlin    doctor        0   
 berlin    lawyer        0   
 berlin    psychologist  1   

See running example at db<>fiddle.