MySQL: Using the dates in a between condition for the results.

Question

Hello to all!

I have a SQL statement in which I do this

... group by date having date between '2010-07-01' and '2010-07-10';

The result looks like:

sum(test)        day
--------------------
20        2010-07-03
120       2010-07-07
33        2010-07-09
42        2010-07-10

So I have these results, but is it possible, that I can write a statement that returns me for every day in the "between" condition a result row in this kind:

sum(test)        day
--------------------
0         2010-07-01
0         2010-07-02
20        2010-07-03
0         2010-07-04
0         2010-07-05
0         2010-07-06
120       2010-07-07
...       ...
42        2010-07-10

Otherwise, if this is not possible, I have to do it in my program logic.

Thanks a lot in advance & Best Regards.

Update: Perhaps it will be better if I will show you the full SQL statement:

select COALESCE(sum(DUR), 0) AS "r", 0 AS "opt", DATE_FORMAT(date, '%d.%m.%Y') AS "day" from (
    select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, 
        a_au.re as RE, a_au.stat as STAT from b_c
        join c on b_c.c_id = c.id
        join a on c.id = a.c_id
        join a_au on a.id = a_au.id
        join revi on a_au.re = revi.re
        join (
            select a.id as ID, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, 
            max(a_au.re) as MAX_RE from b_c
                join c on b_c.c_id = c.id
                join a on c.id = a.c_id
                join a_au on a.id = a_au.id
                join revi on a_au.re = revi.re
                where b_c.b_id = 30 group by ID, date) x on
                x.id = a.id and x.date = date and x.MAX_RE = a_au.rev
                where a_au.stat != 7
            group by ID, x.date)
         AS SubSelTable where date between '2010-07-01' and '2010-07-15' group by date;

---

Update: My new SQL statement (-> Dave Rix):

select coalesce(`theData`.`real`, 0) as 'real', 0 as 'opt', DATE_FORMAT(`DT`.`ddDate`, '%d.%m.%Y') as 'date'
    from `dimdates` as DT 
    left join (
        select coalesce(sum(DUR), 0) AS 'real', 0 AS 'opt', date 
            from (
                select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, a_au.RE as RE, a_au.stat as STAT 
                    from b_c 
                        join c on b_c.c_id = c.id 
                        join a on c.id = a.c_id 
                        join a_au on a.id = a_au.id 
                        join revi on a_au.RE = revi.RE 
                        join ( 
                            select a.id as ID, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, max(a_au.RE) as MAX_RE 
                                from b_c 
                                join c on b_c.c_id = c.id 
                                join a on c.id = a.c_id 
                                join a_au on a.id = a_au.id 
                                join revi on a_au.RE = revi.RE 
                            where b_c.b_id = 30 GROUP BY ID, date
                        ) x 
                    on x.id = a.id and x.date = date and x.MAX_RE = a_au.RE 
                    where a_au.stat != 20 
                    group by ID, x.date
            ) AS SubTable 
        where date between '2010-07-01' and '2010-07-10' group by date) AS theData 
    ON `DT`.`ddDate` = `theData`.`date` where `DT`.`ddDate` between '2010-07-01' and '2010-07-15';

Answer 1

Put the Between Logic in a Where Clause

Select Sum(day), day
From Table
Where day Between date1 and date2
Group By day

EDIT: Having should only be used to filter data in the aggregates... i.e.

Having Sum(day) > 10

Answer 2

Hi Tim, Check out my answer to the following question;

http://stackoverflow.com/questions/3093924/select-all-months-within-given-date-span-including-the-ones-with-0-values

This may be just what you are looking for :)

You can modify your query above as follows (you could integrate this, but this way is simpler!);

SELECT COALESCE(`theData`.`opt`, 0), `DT`.`myDate`
FROM `dateTable` AS DT
LEFT JOIN (
        ... INSERT YOUR QUERY HERE ...
) AS theData
ON `DT`.`myDate` = `theData`.`date`

and you will also need to change the DATE_FORMAT(date, '%d.%m.%Y') AS "day" in your query to just date E.g.

select COALESCE(sum(DUR), 0) AS "r", 0 AS "opt", `date` from

As for @OMG Ponies answer, you will need to pre-populate the dateTable with plenty of rows of data!

Does anyone know how I can post my SQL dump of this table as a file which can be attached? It's quite big, but can be useful...

Answer 3

Assuming that your date column is a DATETIME column, you need to use something to change time values to be the same for proper grouping to happen. IE:

  SELECT SUM(t.test),
         DATE_FORMAT(t.date, '%Y-%m-%d') AS day
    FROM TABLE t
   WHERE t.date BETWEEN @start AND @end
GROUP BY DATE_FORMAT(t.date, '%Y-%m-%d')

But if there's no record for a given date, the date will not appear in the result set. In other words, no dates with zero will appear in your output.

To solve that, you need to LEFT JOIN to a table of dates, which MySQL doesn't have the ability to generate. It can't even generate a list of numbers, so you have to create a table with a single column:

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;

...and populate it:

INSERT INTO numbers (id) VALUES (NULL)

...before you can use the number value to generate a list of dates using the DATE_ADD function:

  SELECT COALESCE(SUM(t.test), 0),
         x.the_date AS day
     FROM (SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL n.id-1 DAY), '%Y-%m-%d') AS the_date
             FROM NUMBERS n) x
LEFT JOIN your_table yt ON DATE_FORMAT(yt.date, '%Y-%m-%d') = x.the_date
    WHERE x.the_date BETWEEN @start AND @end
 GROUP BY x.the_date