select data with mysql

Question

for instance if I have a table like this

total   date
10      2010-01-01
15      2010-01-02
98      2010-01-03
50      2010-01-05

how can I write select statement to get output like this?

total   date
10      2010-01-01
15      2010-01-02
98      2010-01-03
0       2010-01-04
50      2010-01-05

score 1 · Accepted Answer · answered May 13 '11 at 01:53

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

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;

Populate the table using:
```
INSERT INTO NUMBERS
  (id)
VALUES
  (NULL)
```
...for as many values as you need. In this case, the INSERT statement needs to be run at least 31 times.

Use DATE_ADD to construct a list of days, increasing based on the NUMBERS.id value:

SELECT x.dt
  FROM (SELECT DATE(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-01-05' ) x

Use an OUTER JOIN to get your desired output:

   SELECT x.dt,
          COUNT(*) AS total
     FROM (SELECT DATE(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-01-05' ) x
LEFT JOIN YOUR_TABLE y ON y.date = x.dt
 GROUP BY x.dt
 ORDER BY 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.

select data with mysql

1 Answers1

Why Numbers, not Dates?