MYSQL LEFT JOIN and COUNT and GROUP BY

I found several threads concerning that task, but all don't really help me, or I'm not experienced enough to understand it.

I am using mysql and have two tables user and user_rh. I want a list (ordered by names) of usernames with the correspond number of entries in user_rh.

      `user`                `user_rh`
------------------     ------------------
| `uid` | `name` |     | `uid` | `zaid` |
------------------     ------------------
|   1   | Bob    |     |   2   |    4   |
|   2   | John   |     |   2   |    7   |
|   3   | Fred   |     |   3   |    2   |
|   4   | Peter  |     ------------------
------------------

So the result should look like:

--------------------
| `name` | `count` |
--------------------
| Bob    |    0    |
| Fred   |    1    |
| John   |    2    |
| Peter  |    0    |
--------------------

I tried this query:

SELECT
    `user`.`name`,
    `user_rh`.`uid`
FROM
    `user`
LEFT JOIN
    `user_rh`
ON (`user_rh`.`uid`=`user`.`uid`)
ORDER BY
    `user`.`name`

It is going in the right way, but this will not return count values:

------------------
| `name` | `uid` |
------------------
| Bob    |  NULL |
| John   |   2   |
| John   |   2   |
| Fred   |   1   |
| Peter  |  NULL |
------------------

I thought I just add the COUNT() and GROUP BY commands:

SELECT
    `user`.`name`,
    COUNT(`user_rh`.`uid`) AS `count`
FROM
    `user`
LEFT JOIN
    `user_rh`
    ON (`user_rh`.`uid`=`user`.`uid`)
GROUP BY
    `user_rh`.`uid`
ORDER BY
    `user`.`name`

But it shows me something like this. First it delivers a sorted list of names that have count!=0 and the last entry of the list is the first entry of table user which has count=0.

------------------
| `name` | `uid` |
------------------
| John   |   2   |
| Fred   |   1   |
| Bob    |   0   |
------------------

I think, I'm just combining the commands in a wrong way.

---

Your 2nd query is fine. Just group by the user from the first table. Otherwise you would turn your left join into an inner join

SELECT
    `user`.`name`,
    COUNT(`user_rh`.`uid`) AS `count`
FROM
    `user`
LEFT JOIN
    `user_rh`
    ON (`user_rh`.`uid`=`user`.`uid`)
GROUP BY
    `user`.uid, `user`.`name`
ORDER BY
    `user`.`name`