In MySQL, how do I count zero if a LEFT JOIN doesn't match anything?

Question

I'm using MySql 5.5.37. I have a query meant to count the number of times a user logged in

select u.id, IFNULL(count(*), 0) 
FROM user u left join logins l 
on u.id = l.user_id group by u.id;

The problem is, if a user has never logged in, the above is still returning a count of one for that user, probably because the LEFT JOIN returns a NULL when there isn't a matching row. How do I change this so that I get zero if the user has never logged in, and then the appropriate count if they have logged in at least once?

Answer 1

You can use count on one of the columns of right table.

select u.id,
    count(l.user_id)
from user u
left join logins l on u.id = l.user_id
group by u.id

Answer 2

In left joint when the user had never logged in l.user_id = null in the result so try this should work :

select 
    u.id,
    sum(if(l.user_id is not null,1,0)) 
FROM user u left join logins l on u.id = l.user_id group by u.id;