Include zeros in SQL counter query?

Question

I want to be able to return 0 when I do the count, I would prefer not to use joins, since my query doesn't use them.

This is my request.

SELECT count( user_id ) as agencyLogins, 
      DATE_FORMAT(login_date, '%Y-%m-%d') as date 
FROM logins, users 
WHERE login_date >= '2015-02-10%' AND login_date < '2016-02-11%' 
AND logins.user_id = users.id 
GROUP BY DATE_FORMAT(login_date,'%Y-%m-%d')

What he does is the number of times a user visits the site. It does not account for zeros, though, where I want to know when there were no entries in the log.

Answer 1

Try to use an explicit connection in the future, more readable and get rid of these errors. You need a left join:

SELECT t.id,count(s.user_id) as agencyLogins, DATE_FORMAT(s.login_date, '%Y-%m-%d') as date
FROM users t
LEFT OUTER JOIN login s
ON(t.id = s.user_id)
WHERE (s.login_date >= '2015-02-10%' AND s.login_date < '2016-02-11%') or (s.user_id is null)
GROUP BY t.id,DATE_FORMAT(s.login_date,'%Y-%m-%d')

Answer 2

I think the below SQL is useful for you. 2015-02-10% delete the% character in this line.

    SELECT IF(COUNT(user_id) IS NULL,'0',COUNT(user_id)) as agencyLogins, DATE_FORMAT(login_date, '%Y-%m-%d') as date FROM users left join logins on logins.user_id = users.id 
    WHERE date(login_date) >= date('2015-02-10') AND date(login_date) <= date('2016-02-11')
GROUP BY DATE_FORMAT(login_date,'%Y-%m-%d')

Answer 3

    SELECT SUM(agencyLogins), date FROM (
    SELECT count( user_id ) as agencyLogins, 
          DATE_FORMAT(login_date, '%Y-%m-%d') as date 
    FROM logins, users 
    WHERE login_date >= '2015-02-10%' AND login_date < '2016-02-11%' 
    AND logins.user_id = users.id 
    GROUP BY DATE_FORMAT(login_date,'%Y-%m-%d')


    UNION ALL

    SELECT 0,''
    ) AS A 
    GROUP BY DATE