Counting rows from second table

Question

Question by Goldie

I have two tables in mysql database

groups

id|name
_______
1 |red
2 |blue
3 |green
4 |white

and users

id|name  |group
_______________
1 |joe   |1
2 |max   |1
3 |anna  |2
4 |lisa  |2

So… joe and max are in the “red” group, anna and lisa are in the “blue” group.

How can I make simple listing of groups which would contain the number of
persons in that group
For example

red - 2
blue - 2
green - 0
white - 0

Answer 1

Give this a try:

select g.name, count(u.id) from groups g
left join users u on g.id = u.group
group by g.id, g.name

Answer 2

This should work

SELECT g.*, COUNT(DISTINCT u.id) FROM `groups` g
INNER JOIN `users` u on g.id = u.group
GROUP BY u.id