0

Consider the following "messages" table:

_date        message
-------------------------
2012-02-22   hello
2012-02-22   another msg!
2012-03-05   foobar
2012-03-22   testing
2012-03-22   goodbye
2012-03-22   test test

I want to count the number of messages per day, but also want to include all the days on which there are no messages. Say I want to count all messages between 2012-02-01 and 2012-03-29, then the query result should be:

_date        messsages
----------------------
2012-02-01   0
2012-02-02   0
...
2012-02-22   2
2012-02-23   0
...
2012-03-01   0
2012-03-02   0
2012-03-03   0
2012-03-04   0
2012-03-05   1
...
2012-03-22   3
...
2012-03-29   0

Is this possible within MySQL?

pilcrow
  • 56,591
  • 13
  • 94
  • 135
Pr0no
  • 3,910
  • 21
  • 74
  • 121
  • 1
    If the record has no date, how do you know that date is between 2012-02-01 and 2012-03-29? – Curtis Mar 29 '12 at 15:23

4 Answers4

2

Something like this should do it -

SELECT date_range.`date`, COUNT(messages._date)
FROM (
    SELECT '2012-02-01' + INTERVAL (id - 1) DAY AS `date`
    FROM dummy
    WHERE id BETWEEN 1 AND 58
    ORDER BY id ASC
) AS date_range
LEFT JOIN messages
    ON date_range.`date` = messages._date
GROUP BY date_range.`date` ASC

For this example dummy is any table with an integer based id field with a contiguous set covering the required date range. In this case you would need ids from 1 to 58.

user1191247
  • 10,808
  • 2
  • 22
  • 32
0

As indicated here, you will need to use a stored procedure that will create a temporary table of all the dates in the range and join the messages table to that. You cannot return records from a table that does not have them.

Community
  • 1
  • 1
Buggabill
  • 13,726
  • 4
  • 42
  • 47
0

I've found a possibly similar solution using the keyword BETWEEN on another answer here. I've not personally used BETWEEN in SQL before, but it looks promising. I'm unsure on whether it will count 0 for days that don't exist in the table, but it may get you started atleast.

They also mention an uglier method of having a "helper" table to use against a JOIN; this would definitely get you the 0 counts, but would be kind of messy in my opinion. If you decide to go that route, I'd suggest trying a temporary table maybe using CREATE TEMPORARY TABLE.

https://stackoverflow.com/a/895185/1301139

Community
  • 1
  • 1
Demonslay335
  • 776
  • 7
  • 17
  • a BETWEEN x AND y is just another way of writing (a >= x AND a <=y) - [Comparison Function and Operators](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between) – user1191247 Mar 29 '12 at 15:57
0

If you're not running this query often or if you don't want (or can't) create a new table to left join with, you can generate the data dynamically as steted in this question.

Community
  • 1
  • 1
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123