samedi 11 juin 2016

MYSQL query to find sum & add missing dates between periods

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