I have to display how many votes were issued on each day (from the first vote, until the last vote) users voted for poll 2.
My current query works fine; but I'm having trouble when a specific day doesn't have any votes in it. For example, for poll 2, this should be the result:
- May, 11 2017 = 1
- May, 12 2017 = 0
- May, 13 2017 = 0
- May, 14 2017 = 0
- May, 15 2017 = 0
- May, 16 2017 = 0
- May, 17 2017 = 1
- May, 18 2017 = 0
- May, 19 2017 = 0
- May, 20 2017 = 2
...but instead I'm getting this:
- May, 11 2017 = 1
- May, 17 2017 = 1
- May, 20 2017 = 2
So, what I need is that all days that contain no records (between the first and last vote) also appear in the results. This is my current query:
SELECT DATE(poll_vote.date_insert) AS date_insert,
COUNT(poll_vote.id_vote) AS q
FROM poll_vote WHERE poll_vote.id_poll = 2
GROUP BY DATE(date_insert) ORDER BY date_insert
Here's the SQL Fiddle with the example data. Thanks!