title: Counting rows from table
body: <p>I have a problem. It seems easy to resolve, but in fact I don't know why it is not working !</p>
  
  <p>I have two tables :</p>
  
  <pre class="lang-sql prettyprint-override"><code>HOSTS(id, hostgroup_id)
  HOSTGROUPS(id, name)
  </code></pre>
  
  <p>With these inserted rows :</p>
  
  <pre><code>HOSTS
  ________________________
  id    |    hostgroup_id
  ________________________
  1     |    1
  2     |    1
  3     |    2
  4     |    NULL         -- a host can have no hostgroup
  ________________________
  
  
  HOSTGROUPS
  ________________________
  id    |    name
  ________________________
  1     |    ARM
  2     |    Spark
  3     |    Pyro
  ________________________
  </code></pre>
  
  <p>With that, I just want to count number of hosts in each hostgroups.
  Here is my SQL query :</p>
  
  <pre><code>SELECT HG.name, COUNT(H.id) AS count
  FROM HOSTS H, HOSTGROUPS HG
  WHERE H.hostgroup = HG.id
  UNION DISTINCT
  SELECT HG.name, 0 AS count
  FROM HOSTS H, HOSTGROUPS HG
  WHERE (H.hostgroup = HG.id) = FALSE
  </code></pre>
  
  <p>And here is my result :</p>
  
  <pre><code>_____________________
  name     |     count
  _____________________
  ARM      |     2     -- OK
  Spark    |     0     -- NOPE, INTENDED 1
  Pyro     |     0     -- OK
  ARM      |     0     -- NOPE, DUPLICATED ROW
  _____________________
  </code></pre>
  
  <p>And finally, here's what I am waiting for :</p>
  
  <pre><code>_____________________
  name     |     count
  _____________________
  ARM      |     2
  Spark    |     1
  Pyro     |     0
  _____________________
  </code></pre>
  
  <p>Thanks for your answers guys ! :)</p>