Using COUNT() on multiple tables

i’ve got 2 tables (client_names, users).

i want to select all from the client names and count users from the users table where the user matches the client. Which i’m doing. HOWEVER, what it’s doing is no showing client names with 0 users. Is the GROUP BY hiding them?

here’s what i have, but how do i add COUNT() to each row as a


SELECT client_names.name,
       client_names.id,
       COUNT(*) AS user_name
FROM   client_names INNER JOIN users
ON     client_names.id = users.client_id
GROUP BY name;

Is the GROUP BY hiding them?

No, it is the inner join that will restrict the result to those clients which are present in the users table. That is resolved by using an outer join.


SELECT client_names.name,
       client_names.id,
       COUNT(users.client_id) AS user_name
FROM   client_names LEFT OUTER JOIN users
ON     client_names.id = users.client_id
GROUP BY name

Decided to add on to this… and following what you suggested worked, BUT not for counting projects associated with client. I’m getting the same count for projects as there are users. Can anyone suggest something here?

SELECT clients.name, clients.id,
       COUNT(users.client_id) AS userCount,
	   COUNT(client_projects.client_id) AS projectCount
FROM   clients 
LEFT OUTER JOIN users
ON     clients.id = users.client_id
LEFT OUTER JOIN client_projects 
ON     client_projects.client_id = users.client_id
GROUP BY clients.name;

SELECT clients.name,
       clients.id,
       (select count(*)
          from users
         where clients.id = users.client_id) AS userCount,
       (select count(*)
          from client_projects
         where client_projects.client_id = users.client_id) AS projectCount
FROM   clients

You are counting the combination of all users and projetcs.

thank you.