Grouping by date, return string, even if no records are found

Question

I have a query that groups all the records from a table and groups them by a datetime column. All this works great:

SELECT SUM( `value` ) AS `sum` , DATE(`datetime`) AS `dt`` FROM `entry` WHERE entryid = 85 AND DATETIME BETWEEN '2010-01-01' AND '2010-03-01' GROUP BY `dt` ORDER BY `datetime` 

The problem is that I need to return the string, even if nothing was found, with the value set to 0. I assume that there is some kind of MYSQL function that will take care of this, It seems he found it.

Thanks!

Answer 1

MySQL does not have recursive functionality, so you just have to use the NUMBERS table trick -

• Create a table containing only incremental numbers - easy to do with auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

• Fill in the table using:

   INSERT INTO NUMBERS (id) VALUES (NULL) 

  ... for the number of values ​​you need.

• Use DATE_ADD to create a list of dates by incrementing days based on NUMBERS.id. Replace "2010-01-01" and "2010-03-01" with the corresponding start and end dates (but use the same format, YYYY-MM-DD) -

   SELECT x.* FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY) FROM numbers n WHERE DATE_ADD('2010-01-01', INTERVAL n.id -1 DAY) <= '2010-03-01' ) x 

• LEFT JOIN per data table based on the datetime part:

    SELECT DATE(x.dt) AS dt, COALESCE(SUM(e.value), 0) AS sum_value FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY) AS dt FROM numbers n WHERE DATE_ADD('2010-01-01', INTERVAL n.id -1 DAY) <= '2010-03-01' ) x LEFT JOIN ENTRY e ON DATE(e.datetime) = x.dt AND e.entryid = 85 GROUP BY DATE(x.dt) 

Why numbers, not dates?

Simple dates can be generated based on a number, as in the example above. It also means using a single table, for example, for each data type.