I have a table with columns userid, date. so if a user log in it will add a new entry. i want a query to find the sum of user logged in a date range & if no users are logged in a specific date i want it to show 0 for that date.
userid date
1 1.7.2016
2 1.7.2016
4 3.7.2016
6 5.7.2016
1 5.7.2016
2 5.7.2016
so i want result of above table as
date total
1.7.2016 2
2.7.2016 0
3.7.2016 1
4.7.2016 0
5.7.2016 3
i tied using
SELECT DISTINCT(date(`date`)) AS `day`, count( * ) AS `count`
FROM `users`
WHERE `date` BETWEEN DATE_SUB(CURDATE(), INTERVAL 28 DAY) AND CURDATE()
GROUP BY `day`
ORDER BY `day` ASC
but its not returning 0 for missing dates.
Aucun commentaire:
Enregistrer un commentaire