iArcadia iArcadia - 7 months ago 19
SQL Question

Counting rows from table

I have a problem. It seems easy to resolve, but in fact I don't know why it is not working !

I have two tables :

HOSTS(id, hostgroup_id)
HOSTGROUPS(id, name)


With these inserted rows :

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
________________________


With that, I just want to count number of hosts in each hostgroups.
Here is my SQL query :

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


And here is my result :

_____________________
name | count
_____________________
ARM | 2 -- OK
Spark | 0 -- NOPE, INTENDED 1
Pyro | 0 -- OK
ARM | 0 -- NOPE, DUPLICATED ROW
_____________________


And finally, here's what I am waiting for :

_____________________
name | count
_____________________
ARM | 2
Spark | 1
Pyro | 0
_____________________


Thanks for your answers guys ! :)

Answer

Try this:

SELECT hg.name,
(SELECT COUNT(*)
FROM HOSTS h
WHERE h.hostgroup_id = hg.id)
FROM HOSTGROUPS hg