Q:

select group , count zeros

I have a table users like this:
id user refid
1  a    null
2  b    1
3  c    1
4  d    2
5  f    3

I need to select the users, grouping by id and the count number of each refid for each user  For example,
id    user count
1      a     2
2      b     1
3      c     1
4      d     0
5      4     0

This is what I have tried:
SELECT u1 . id,u1 . user , count( u1.id ) count FROM users u1 
LEFT JOIN users u2 ON u1.id = u2.refid GROUP BY u1.id

which returns
id    user count
1      a     2
2      b     1
3      c     1

How can i have mysql return both those whose id has not been used as refid?

A:

You can use SUM(IF(...,0,1)) to count only rows with a certain property. I think the following should work, although I am not sure, why the last two rows are missing in your output.
SELECT u1.id, u1.user, SUM(IF(u2.id IS NULL,0,1)) count
FROM users u1 
LEFT JOIN users u2 ON u1.id=u2.refid
GROUP BY u1.id

