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?

Marcus Adams · Accepted Answer · 2010-03-28 15:22:30Z

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.

answered Mar 28, 2010 at 15:22

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

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

Comments

lexu · Accepted Answer · 2010-03-28 14:28:42Z

2

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

answered Mar 28, 2010 at 14:28

lexu

8,8495 gold badges48 silver badges63 bronze badges

Collectives™ on Stack Overflow

MySQL count problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related