How Do I Count Users Per Group with MySQL?

1
votes

I have a table of groups:

  • id -- a unique ID (primary key)
  • group -- a group name

I have a table of users:

  • username -- a unique string (primary key)
  • group_id -- a foreign key link to group.id

In this case, a user is only going to be in one single group. So, in MySQL, how do I list out groups and their count of member users?

I tried using a subselect and count, but it ends up showing the same count on all groups.

2
mysqlsqlcountgroup-bysql-subselect

2 Answers

7
votes
SELECT group.id,COUNT(users.username) FROM groups
INNER JOIN users ON users.group_id=groups.id
GROUP BY groups.id

You can use a LEFT JOIN instead if you want to display empty groups per @Michael's comment

0
votes
select g.group, count(*) as group_count
from groups g, users g
where g.id=u.group_id
group by g.groupname