CODEWITHSUNDEEP
mysqlsqlcountrows
Kévin Bibollet
Kévin Bibollet

Reputation: 3623

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 ! :)

Upvotes: 2

Views: 75

Answers (3)

Periklis Douvitsas
Periklis Douvitsas

Reputation: 2491

SELECT HG.name, COUNT(H.id) AS count
FROM HOSTS H, HOSTGROUPS HG
WHERE H.hostgroup = HG.id(+)
group by H.id;

Hope this helps

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

I think you can use a left join with group by 

SELECT HG.name, COUNT(*) AS count
FROM HOSTS H 
LEFT JOIN  HOSTGROUPS HG ON ( H.hostgroup = HG.id)
GROUP BY HG.name;

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

Try this:

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

Upvotes: 2

Related Questions