MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE cities(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,name VARCHAR(8) NOT NULL
);
INSERT INTO cities(id,name) VALUES (1,'london');
INSERT INTO cities(id,name) VALUES (2,'new york');
INSERT INTO cities(id,name) VALUES (3,'berlin');
CREATE TABLE categories(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,name VARCHAR(20) NOT NULL
);
INSERT INTO categories(id,name) VALUES (1,'doctor');
INSERT INTO categories(id,name) VALUES (2,'lawyer');
INSERT INTO categories(id,name) VALUES (3,'psychologist');
CREATE TABLE orgs(
   id          INTEGER  NOT NULL
  ,name        VARCHAR(14) NOT NULL
  ,category_id INTEGER  NOT NULL
  ,city_id     INTEGER  NOT NULL
);
INSERT INTO orgs(id,name,category_id,city_id) VALUES (1,'john doe',2,1);
INSERT INTO orgs(id,name,category_id,city_id) VALUES (2,'max muster',1,2);
INSERT INTO orgs(id,name,category_id,city_id) VALUES (3,'erika schmidt',3,3);
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


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

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