title: MYSQL LEFT JOIN and COUNT and GROUP BY
body: <p>I found several threads concerning that task, but all don't really help me, or I'm not experienced enough to understand it.</p>
  
  <p>I am using mysql and have two tables <code>user</code> and <code>user_rh</code>. I want a list (ordered by names) of usernames with the correspond number of entries in <code>user_rh</code>.</p>
  
  <pre><code>      `user`                `user_rh`
  ------------------     ------------------
  | `uid` | `name` |     | `uid` | `zaid` |
  ------------------     ------------------
  |   1   | Bob    |     |   2   |    4   |
  |   2   | John   |     |   2   |    7   |
  |   3   | Fred   |     |   3   |    2   |
  |   4   | Peter  |     ------------------
  ------------------
  </code></pre>
  
  <p>So the result should look like:</p>
  
  <pre><code>--------------------
  | `name` | `count` |
  --------------------
  | Bob    |    0    |
  | Fred   |    1    |
  | John   |    2    |
  | Peter  |    0    |
  --------------------
  </code></pre>
  
  <p>I tried this query:</p>
  
  <pre><code>SELECT
      `user`.`name`,
      `user_rh`.`uid`
  FROM
      `user`
  LEFT JOIN
      `user_rh`
  ON (`user_rh`.`uid`=`user`.`uid`)
  ORDER BY
      `user`.`name`
  </code></pre>
  
  <p>It is going in the right way, but this will not return count values:</p>
  
  <pre><code>------------------
  | `name` | `uid` |
  ------------------
  | Bob    |  NULL |
  | John   |   2   |
  | John   |   2   |
  | Fred   |   1   |
  | Peter  |  NULL |
  ------------------
  </code></pre>
  
  <p>I thought I just add the COUNT() and GROUP BY commands:</p>
  
  <pre><code>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`
  </code></pre>
  
  <p>But it shows me something like this. First it delivers a sorted list of names that have <code>count</code>!=0 and the last entry of the list is the first entry of table <code>user</code> which has <code>count</code>=0.</p>
  
  <pre><code>------------------
  | `name` | `uid` |
  ------------------
  | John   |   2   |
  | Fred   |   1   |
  | Bob    |   0   |
  ------------------
  </code></pre>
  
  <p>I think, I'm just combining the commands in a wrong way.</p>