Hi everyone...

I am running this query which grabs activity per day for 10 days

SELECT DATE_FORMAT( created, "%c/%d/%Y" ) AS `day` , COUNT( id ) AS
'activity'
FROM `tracking`
WHERE url_id = 1
GROUP BY DAY( created )
LIMIT 0 , 10

Which successfully returns activity by day:

day activity
1/26/2010 5
2/01/2010 1
2/02/2010 1
2/03/2010 1
2/05/2010 8
2/06/2010 5

However, I am trying to get it to return "0"s for empty days, such as:

day activity
1/26/2010 5
1/27/2010 0
1/28/2010 0
1/29/2010 0
1/30/2010 0
2/01/2010 1
2/02/2010 1
2/03/2010 1
2/05/2010 8
2/06/2010 5

Thanks in advance!

On 6 Feb, 21:57, John R <john... (AT) gmail (DOT) com> wrote:
http://tinyurl.com/yc6fz4h

/Lennart

On Feb 6, 3:11*pm, Lennart <erik.lennart.jons... (AT) gmail (DOT) com> wrote:
I have seen that method elsewhere, however I am still a bit confused
on what I would need. A massive table of dates from now to the
foreseeable future? Or a constantly changing table that just has the
most recent 10 days dates?

On Sat, 06 Feb 2010 14:25:49 -0800, John R wrote:

As I read it, a table with 31 rows containing the numbers 1 to 31. It's
only necessary to cover all the possible days of the month.

On Feb 7, 6:28*am, Derek Turner <frde... (AT) cesmail (DOT) net> wrote:
Thanks for the response. I still don't understand how this would work.
My dates are stored in the DATETIME format. If I have a list of 1-31,
how would I just match the last 10 days? Wouldn't you need the month
and even the year?

On Mon, 08 Feb 2010 08:09:01 -0800, John R wrote:


No, they're not. They are stored as the number of seconds since 1 January
1970. MySQL coverts that to the DATETIME format when you query the data
base. There are a number of functions in MySQL that allow you to get the
data out in whatever format you want. e.g.

SELECT UNIX_TIMESTAMP(eventDate) AS unixdate, ....

by using these in a complex query you can use an outside join on the 31-
row table to return your null values. That's how I read it, but I've
never done it!