How to group number of rows per day also in no-row days in MySQL?

Question

So i have this table. It has some hundreds of rows. Every row has a datetime field in it. And what i need to acomplish is to get how much rows there are in given period of time but not for the whole period but for every day in this period. Till this point i know what to do. But moreover i need to have also rows for the days that does not have any row in the table with value 0.

So for example:

2012-01-01 12:13
2012-01-01 43:32
2012-01-03 23:32

Should give me the result like this:

2012-01-01 2
2012-01-02 0
2012-01-03 1

Anyone can help??

possible duplicate of [Get date even if it doesn't exist in table from SQL SELECT statement](http://stackoverflow.com/questions/9228262/get-date-even-if-it-doesnt-exist-in-table-from-sql-select-statement) — Shakti Singh, Feb 29 '12 at 12:39
possible duplicate of [Mysql: Select all data between two dates](http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates) — kapa, Feb 29 '12 at 12:41

score 3 · Accepted Answer · answered Feb 29 '12 at 12:43

To deal with dates with 0 corresponding records, my normal practice is to use a calendar table to join on.

For example, create a table with one field called calendar_date and populate it with every date from 1st Jan 2000 to 31st Dec 2070, or some other range that suits your reporting purposes.

Then use something like...

SELECT
  calendar.calendar_date,
  COUNT(*)
FROM
  calendar
LEFT JOIN
  yourData
    ON  yourData.timeStamp >= calendar.calendar_date
    AND yourData.timeStamp <  calendar.calendar_date + 1
WHERE
      calendar.calendar_date >= '01 Jan 2012'
  AND calendar.calendar_date <  '04 Jan 2012'
GROUP BY
  calendar.calendar_date

This table can have many extra uses, such as flagging bank holidays, starts of weeks and months. With prudent uses of flags and indexes, you can get a lot out of it.

Just like you normally do. `CREATE TABLE calendar (calendar_date DATETIME)`, etc. Then run a loop or something to populate the values. Once you've done that once, you then have a dimension table to join your data on to. — MatBailie, Feb 29 '12 at 12:47

score 2 · Answer 2 · answered Feb 29 '12 at 13:00

If you don't have a table with a list of dates in it, you can simulate one with something like the following query:

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) calendar_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where calendar_date between ? /*start of date range*/ and ? /*end of date range*/

- so Dems' query could become:

SELECT
  calendar.calendar_date,
  COUNT(*)
FROM
  (select * from 
    (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) calendar_date from
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
   WHERE calendar_date >= '01 Jan 2012'
     AND calendar_date <  '04 Jan 2012'
  ) calendar
LEFT JOIN
  yourData
    ON  yourData.timeStamp >= calendar.calendar_date
    AND yourData.timeStamp <  calendar.calendar_date + 1

score 0 · Answer 3 · edited Feb 29 '12 at 13:23

0

try

SELECT DATE_FORMAT(YourDate, '%Y-%m-%d') as YourDate, COUNT(*)
FROM TableName where YourDate= 'date' 
GROUP BY YourDate

edited Feb 29 '12 at 13:23

Michał Powaga

22,561
8
51
62

answered Feb 29 '12 at 12:42

Ghost Answer

1,458
1
17
41

1

Like two previous (oh, now deleted) answers... `This doesn't deal with days with 0 rows` – MatBailie Feb 29 '12 at 12:43

How to group number of rows per day also in no-row days in MySQL?

3 Answers3

Linked