how to count on one column from a table group by another column in another table?

Question

I'm working on two tables called users and department

users

id	id_dep	role
1	1	0
2	2	1
3	2	3
4	1	0
5	3	0
6	1	2
7	2	0
8	5	1

department

id	name
1	math
2	physic
3	chemistry
4	economy
5	history

What I need exactly is to retrieve all information from department Table with the number of users elements that are from the department table ( group by ) and role = 0.

Expected results:

name	numberOfUsers
math	2
physic	1
chemistry	1
economy	0
history	0

I have tried to execute the query but it did not give me any result:

SELECT
  count(users.id) as numberOfUsers,
  department.name as name
FROM users
LEFT JOIN department ON users.id_dep = department.id
WHERE users.role = 0
GROUP BY name;

What can I modify to get the needed result?

Answer 1

You are left-joining the wrong way round

SELECT
  COUNT(u.id) as numberOfUsers,
  d.name as name
FROM department AS d
LEFT JOIN users AS u ON u.id_dep = d.id AND u.role = 0
GROUP BY d.name;

db<>fiddle