mysql date list with count even if no data on specific date

Question

Possible Duplicate:
MySQL how to fill missing dates in range?

I'm trying to make a graph from mysqldata,

Postid | date       | text
1      | 2012-01-01 | bla
2      | 2012-01-01 | bla
3      | 2012-01-02 | bla
4      | 2012-01-02 | bla
5      | 2012-01-04 | bla
6      | 2012-01-04 | bla
7      | 2012-01-05 | bla

Now, I'd like to get the number of posts on every day, INCLUDING dates with zero. For example, i'd like to be able to get the first week like this:

date       | count(Postid)
2012-01-01 | 2
2012-01-02 | 2
2012-01-03 | 0
2012-01-04 | 2
2012-01-05 | 1
2012-01-06 | 0
2012-01-07 | 0

I'm looking for a generic solution, where i don't have to specify every date. Any suggestions?

It is very difficult to do row generation in mysql, without a table of arbitrary size (so I guess you could), with rownum = rownum + 1 tricks). I would suggest if you need to do this on the database side, that you create a table containing a suitable range of dates to do an outer join against in order to get the data that you need. PostgreSQL has the function 'generate_series' which covers row generation, and would make this incredibly easy without another table. — Horus, Oct 06 '12 at 15:41
[THIS ARTICLE (*click here*)](http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/) may guide you in solving your problem. — John Woo, Oct 06 '12 at 15:45

Ross Smith II · Answer 1 · 2014-04-28T21:33:40.667

Assuming your postids are contiguous in your table, then this query:

SELECT
  DATE_FORMAT(b.date, '%Y-%m-%d') date,
  COUNT(c.postid)
FROM
(
  SELECT
    (SELECT MAX(date) FROM ex) + INTERVAL 3 DAY - INTERVAL a.postid DAY AS date
  FROM
    ex a
) b
LEFT JOIN
  ex c ON c.date = b.date
GROUP BY
  b.date
ORDER BY
  b.date

produces:

date    COUNT(c.postid)
2012-01-01  2
2012-01-02  2
2012-01-03  0
2012-01-04  2
2012-01-05  1
2012-01-06  0
2012-01-07  0

See http://sqlfiddle.com/#!2/2cf8d/2

If your postids are not contiguous, then you can use an ids table of contiguous ids:

SELECT
  DATE_FORMAT(b.date, '%Y-%m-%d') date,
  COUNT(c.postid)
FROM
(
  SELECT
    (SELECT MAX(date) FROM ex) + INTERVAL 3 DAY - INTERVAL a.id DAY AS date
  FROM
    ids a
) b
LEFT JOIN
  ex c ON c.date = b.date
GROUP BY
  b.date
ORDER BY
  b.date DESC
LIMIT 7

See http://sqlfiddle.com/#!2/13035/1

score 2 · Answer 2 · answered Oct 06 '12 at 15:44

in MySQL, I would suggest creating a Calendar table with the dates listed. Then you will join on that table. Similar to this:

CREATE TABLE Table1(`Postid` int, `date` datetime, `text` varchar(3));

INSERT INTO Table1(`Postid`, `date`, `text`)
VALUES
    (1, '2011-12-31 17:00:00', 'bla'),
    (2, '2011-12-31 17:00:00', 'bla'),
    (3, '2012-01-01 17:00:00', 'bla'),
    (4, '2012-01-01 17:00:00', 'bla'),
    (5, '2012-01-03 17:00:00', 'bla'),
    (6, '2012-01-03 17:00:00', 'bla'),
    (7, '2012-01-04 17:00:00', 'bla');

CREATE TABLE Table2(`date` datetime);

INSERT INTO Table2(`date`)
VALUES('2011-12-31 17:00:00'),
    ('2012-01-01 17:00:00'),
    ('2012-01-02 17:00:00'),
    ('2012-01-03 17:00:00'),
    ('2012-01-04 17:00:00'),
    ('2012-01-05 17:00:00'),
    ('2012-01-06 17:00:00'),
    ('2012-01-07 17:00:00'),
    ('2012-01-08 17:00:00');

select t2.date, count(postid)
from table2 t2
left join table1 t1
  on t2.date = t1.date
group by t2.date

See SQL Fiddle with Demo

mysql date list with count even if no data on specific date

2 Answers2

Linked