Mysql计算连接两个表后字段上的用户总数

Question

Need to know what is the syntax to get the total number of users on each admin roles and print it.

Roles
id  |  name
---    ----
1   |  admin
2   |  moderator
3   |  user

Table-2

users
id  |  name | role
--     ----   ----
1   |  a    |  1
2   |  b    |  2
3   |  c    |  2
4   |  d    |  2
5   |  e    |  3
6   |  f    |  3
7   |  g    |  3
8   |  h    |  1
9   |  i    |  1

Code

 <?php
   $sql = "select r.name, r.id from role r, users u where r.id=u.role";
   $result = mysql_query($sql);
   $count = 0;
   while($row = mysql_fetch_array($result))
    {
      if($count!=$row['1'])
        {
          echo $row['0']."- NEED TOTAL NUMBER OF USERS ON EACH ROLES TO BE PRINTED HERE.";
          $count=$row['1'];
        }
?>

Answer 1

SELECT  COUNT(b.role)
FROM    roles a
        LEFT JOIN  users b
            ON a.ID = b.role
WHERE   a.name = 'admin'

• SQLFiddle Demo

UPDATE 1

SELECT  a.name, COUNT(b.role) totalCount
FROM    roles a
        LEFT JOIN  users b
            ON a.ID = b.role
GROUP BY a.Name

• SQLFiddle Demo