views:

87

answers:

1

I have a query 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!

+3  A: 

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.

OMG Ponies