Get total views per day including leaving "0 views" for a day that has no record

Question

I am creating a graph where I can get the total views everyday for a certain range, or as long it goes back.

The problem I am having is to fill a default number of 0 when no views has been made for a certain day, some days there may be absolutely no views in a day so I need MySQL to return a default of 0 when none is found - I have no idea how to do this.

This is the query I use to get the total views a day:

SELECT DATE(FROM_UNIXTIME(v.date)) AS date_views,
       COUNT(v.view_id) AS total_views
FROM 
(
    views v
)
GROUP BY date_views
ORDER BY v.date DESC

My results return this:

+------------+-------------+
| date_views | total_views |
+------------+-------------+
| 2012-10-17 |           2 |
| 2012-10-15 |           5 |
| 2012-10-14 |           1 |
| 2012-10-10 |           7 |
+------------+-------------+

However there are missing days that I want to return 0 for it, as 2012-10-16, 2012-10-11, 2012-10-12, 2012-10-13 is not included.

So, for example:

+------------+-------------+
| date_views | total_views |
+------------+-------------+
| 2012-10-17 |           2 |
| 2012-10-16 |           0 |
| 2012-10-15 |           5 |
| 2012-10-14 |           1 |
| 2012-10-13 |           0 |
| 2012-10-12 |           0 |
| 2012-10-11 |           0 |
| 2012-10-10 |           7 |
+------------+-------------+

Would be returned.

How would this be approached?

Related: http://stackoverflow.com/questions/2922637/mysql-selecting-default-value-if-there-are-no-results http://stackoverflow.com/questions/4977951/mysql-if-row-doesnt-exist-grab-default-value — twodayslate, Oct 17 '12 at 14:06

score 3 · Answer 1 · answered Oct 17 '12 at 14:10

3

When I did this a couple of years ago I created an empty array with the date as key and the default value 0. Then I simply looped through the result att changed the value for those dates I had.

for each($result as $row){
   $date_stats_array[$row['date']] = $row['value'];
}

answered Oct 17 '12 at 14:10

Esuk

131
1
4

So you fill in the blanks that are missing between a range? What happens to situations when it comes to months that has 30/31 days? – MacMac Oct 17 '12 at 14:16

score 1 · Answer 2 · edited May 23 '17 at 11:43

In situations like this I create a temporary table which I fill with all the dates you want. After that, you can use that table to join your original query against.

To fill the table you can use this procedure:

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');

Courtesy of https://stackoverflow.com/a/10132142/375087

Get total views per day including leaving "0 views" for a day that has no record

2 Answers2

Linked