Grouping by date, return row even if no records found

I have a query开发者_Python百科 that groups all entries from a table and groups them by the datetime column. This is all working 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, I need it to return a row even if nothing is found, with the value set to 0. I assume there's some MYSQL function that'll take care of this but can't seem to find it.

Thanks!

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. Create a table that only holds incrementing numbers - easy to do using an 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;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-03-01" with your respective 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
   

4. LEFT JOIN onto your table of data based on the datetime portion:

      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 the number, like in the example I provided. It also means using a single table, vs say one per data type.