MySQL Select return wrong NULL value with COUNT

Question

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 ?

`NULL` values can do weird things with comparisons. See what happens if you do something like `GROUP BY IFNULL(referer_id, -1)`. — Jacob See, May 04 '16 at 00:32

ghenghy · Accepted Answer · 2016-05-04T01:10:47.377

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;

score 1 · Answer 2 · edited May 23 '17 at 10:30

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?

score 0 · Answer 3 · answered May 04 '16 at 04:24

0

SELECT `referer_id`,count(NVL(`referer_id`,0)) 
FROM `users`
GROUP BY `referer_id`

answered May 04 '16 at 04:24

Adesh

34
4

Unfortunately, his question pertains to mysql, which has no NVL function. ifnull is the mysql equivalent. However, even if you do this query w/ ifnull, it still doesn't return results for those that have no referers, which is what the OP wants. – ghenghy May 04 '16 at 18:49

MySQL Select return wrong NULL value with COUNT

3 Answers3