Making a query that counts grouped by day

[ntbgl]

I am having the hardest time building this query.

I have a mysql table that has different stories, and a published date in the form of "YYYY-MM-DD HH:MM:SS"

What I would like to do would be for any given month, to build an array with the date, and the number of stories published on that date.

This is what I have so far, but I know it isn't correct.

$query=mysql_query("SELECT COUNT(*) FROM table WHERE pubdate>'2009-01-01 00:00:00' AND pubdate<'2009-01-31 24:00:00' GROUP BY pubdate");
$array=...

Any help would be greatly appreciated.

[Morgenhund]

Hmm.. not sure... probably grouping by date does not work because you have time part in pubdate as well, and that is different for each story.

Probably, you should use something like that (untested):
$query=mysql_query("SELECT COUNT(*) FROM table WHERE pubdate>'2009-01-01 00:00:00' AND pubdate<'2009-01-31 24:00:00' GROUP BY day(pubdate)");

Or even:
$query=mysql_query("SELECT COUNT(*) FROM table WHERE year(pubdate) = 2009 and month(pubdate) = 1 GROUP BY day(pubdate)");

[ntbgl]

Thank you for the reply, but unfortunatly both didn't work. MySQL error on both.

It didn't like the day(pubdate).

Shy of doing a query for each day, anyone have an idea?

Thanks

[coopster]

I would leave the time portion out altogether:

SELECT 
 pubdate, 
 COUNT(pubdate) AS count 
FROM table 
WHERE pubdate BETWEEN '2009-01-01' AND '2009-01-31' 
GROUP BY pubdate 
;

[ntbgl]

Thank you coopster for your reply, you use some formating that helped make my query better, but I'm still having problems actually grouping the dates.

The following code:

SELECT pubdate, COUNT(pubdate) AS count FROM table WHERE pubdate BETWEEN '2009-01-01' AND '2009-01-31' GROUP BY pubdate ;

Gives me this sample output:

2009-01-01 01:00:00, 1
2009-01-01 10:00:03, 2
2009-01-01 10:28:46, 1
2009-01-01 10:30:03, 2
2009-01-01 10:58:40, 1
2009-01-01 11:00:03, 1
...

Will I need to break up this column into one that's just date, and one that's just time to do this? I thought mysql was very flexible when using the right column types.

[ntbgl]

I tried playing around with it more, and looks like with this newly formated query, I can now use the GROUP BY day(pubdate);

It works great! Thank you both!

[coopster]

Duh. I forgot the time portion would have an impact on the GROUP BY. Sorry about that, and I'm glad you were able to move on from there. Nice job.