Select Group by from Same Table

Question

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 refid and the count of each refid for each user. For example,

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

This is what I have tried:

SELECT user, refid, count(*) cnt FROM `users` group by refid

However, this gives me the wrong user for each value. How can I get the correct user for each value?

Answer 1

I guess this is what you're looking for. Basically, you're missing a join there.

select u1.id, u1.user, count(u1.id) cnt from mlm_users u1
join mlm_users u2 on u1.id = u2.refid
group by u1.id, u1.user

This will return the id, user and the amount of referrals each of them have (only for the ones that have at least one referral).

Answer 2

You need to group by the user and refid

SELECT user, refid, count(*) cnt FROM `mlm_users` group by user,redid