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
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
check if this works....
SELECT COUNT(*), groups.name FROM groups, users WHERE users.group=groups.id GROUP BY groups.name
UPDATE
SELECT groups.name, COUNT(users.*) FROM groups LEFT JOIN users
ON groups.id=users.group GROUP BY groups.name
this will keep the colors even if they dont have any name related
Try this
SELECT COUNT(g.id) as count, g.name
FROM groups as g
LEFT JOIN users as u
ON u.group = g.id
GROUP BY g.id
Most the other answers are basically correct, but forgot an important detail: GROUP is a reserved word in SQL, so your column name must be escaped:
SELECT groups.name, COUNT(*) AS total_members
FROM groups
LEFT OUTER JOIN users
ON users.`group` = groups.id
GROUP BY groups.id
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
maybe this should work
SELECT g.name, COUNT( u.id ) AS Totoal
FROM `groups` g
INNER JOIN `users` u ON g.id = u.group
GROUP BY g.id