Let's say I have a users table with two column, id and referer_id
If the user was refered by someone else, his referrer will be in referer_id. If he signed up by himself then the referer_id will be NULL
I want to count how many referred users a user has.
I have a query like this
SELECT `referer_id`,count(`referer_id`) FROM `users`
GROUP BY `referer_id`
As you can see the NULL count is 0 , but I have a lot of users who was not refered by anybody. How can I solve this ?
I don't like this, I think there's a more elegant solution out there, but it works and may help you find that better solution.
select
t1.id,
ifnull(t3.ct, 0)
from
temp t1
left join
(select
t2.referer_id,
count(t2.referer_id) as ct
from temp t2
group by t2.referer_id) t3
on t1.id = t3.referer_id;
With a little more thought, here's an option that avoids the subselect:
select t1.id, ifnull(count(t2.referer_id), 0)
from temp t1
left join temp t2 on t1.id = t2.referer_id
group by t1.id;
Even though I can't explain what reason caused this issue, I figured it out with another solution, like this;)
SELECT `referer_id`,
if(`referer_id` is null, @num := @num + 1, count(`referer_id`)) as referer_id_cnt
FROM `users`, (select @num := 0) tmp
GROUP BY `referer_id`
Hmm, what I've wrote above is definitely not a proper answer. Actually this will help you.
SELECT `referer_id`,count(1) FROM `users`
GROUP BY `referer_id`
And take a look of this link How to count NULL values in MySQL?
SELECT `referer_id`,count(NVL(`referer_id`,0))
FROM `users`
GROUP BY `referer_id`
NULLvalues can do weird things with comparisons. See what happens if you do something likeGROUP BY IFNULL(referer_id, -1).