How to get days where there are no counts

Question

Possible Duplicate:
What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Every "record" created in some_table has a "date".

some_table:

-------------------------
id | record | my_date
-------------------------
1  | rec_a  | 2012-08-01
2  | rec_b  | 2012-08-02
3  | rec_c  | 2012-08-02
4  | rec_d  | 2012-08-04
5  | rec_e  | 2012-08-06
6  | rec_f  | 2012-08-06

my_query:

start_date and end_date come from an input

SELECT
    my_date, COUNT(my_date) AS _count
FROM
    some_table
WHERE
    my_date
BETWEEN
    start_date
AND
    end_date
GROUP BY
DAY (my_date)

array:

while($result = mysql_fetch_assoc($my_query))
{
    $dataset[] = array($result['my_date'],intval($result['_count']));
}

json_encode($dataset);

this will make:

["2012-08-01",1]
["2012-08-02",2]
["2012-08-04",4]
["2012-08-06",6]

what is the best way to get the days where there are no counts like this?

["2012-08-01",1]
["2012-08-02",2]
["2012-08-03",0] <--
["2012-08-04",4]
["2012-08-05",0] <--
["2012-08-06",6]

This seems to be similar to your question: http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe — themanatuf, Aug 28 '12 at 18:19

score 0 · Answer 1 · answered Aug 28 '12 at 18:23

0

Probably the easiest way to do this is to create a table with all possible dates in it. You would then select from that table and left join to your existing table.

It might seem hacky, but this is a very common pattern, especially in data warehousing.

answered Aug 28 '12 at 18:23

Eric Petroelje

58,601
9
123
175

So I should create a table with years or just days and months? If I create with years I have to create rows for the next X years... If without years what about the leap years that have 366 days. Are you sure this is common pattern? It seems very hacky. I've seen something about temporary tables... I was thinking about creating a temp table with the date range from the input and LEFT JOIN it with the actual results... – Joao Belchior Aug 28 '12 at 18:45
@Joao - The usual pattern is to create a table with the full date as the key. You can then have other columns like year, month, day, day of week, etc... whatever you find useful. And yes, you would create rows for the next X years (probably via a script) which isn't a big deal. 100 years of data is only 36,500 rows, which is nothing for a database. – Eric Petroelje Aug 28 '12 at 20:34

How to get days where there are no counts

1 Answers1