SQL Output of NULL values

Question

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

cities

id	name
1	london
2	new york
3	berlin

categories

id	name
1	doctor
2	lawyer
3	psychologist

orgs

id	name	category_id	city_id
1	john doe	2	1
2	max muster	1	2
3	erika schmidt	3	3

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.

Move the o.city_id condition from WHERE to ON to get true LEFT JOIN result. — jarlh
– jarlh, Commented Aug 15, 2022 at 19:11

The Impaler · Accepted Answer · 2022-08-15 19:46:21Z

1

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.

answered Aug 15, 2022 at 19:46

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Output of NULL values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related