CODEWITHSUNDEEP
mysqlsqlgroup-by
Smith
Smith

Reputation: 5951

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?

Upvotes: 1

Views: 104

Answers (2)

Barmar
Barmar

Reputation: 780714

This works:

SELECT u1 . id,u1 . user , count( u2.refid ) count FROM users u1 
LEFT JOIN users u2 ON u1.id = u2.refid GROUP BY u1.id

COUNT only counts non-null values, so you need to specify a column in u2, since that will be NULL when there's no match.

FIDDLE

Upvotes: 2

nosid
nosid

Reputation: 50024

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

Upvotes: 1

Related Questions