MySQL count problem

Question

I have 3 tables users(id,name),groups(id,name) and users_groups(user_id,group_id). users and groups have many to many relationship, so the third one is for storing users and groups relations. I would like to select all the data from groups with user count in each group. So far I came up with this:

SELECT groups.*, COUNT(users_groups.user_id) AS user_count
FROM groups
LEFT JOIN users_groups ON users_groups.group_id = groups.id

The problem is that query result is not returning any of groups which has no users (users_groups doesnt have any records with group_id of those groups).

---

How should I create my query to select all the groups and they user count, or user count as 0 if there are no users for that group?

Answer 1

try something along the lines of

SELECT groups.*
     , sum( if (ISNULL(users_groups.user_id),0,1)) AS user_count
FROM groups
LEFT JOIN users_groups 
  ON users_groups.group_id = groups.id
GROUP BY groups.id

Answer 2

You just need to add the GROUP BY clause:

SELECT groups.*, COUNT(users_groups.user_id) AS user_count
FROM groups
LEFT JOIN users_groups ON users_groups.group_id = groups.id
GROUP BY groups.id

You'll then get 0 for user_count for each group that doesn't have corresponding users_groups records.