Welcome to WebmasterWorld Guest from 54.226.67.166

Forum Moderators: open

Message Too Old, No Replies

Making a query that counts grouped by day

     

ntbgl

2:38 am on Feb 1, 2009 (gmt 0)

5+ Year Member



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

10:48 pm on Feb 2, 2009 (gmt 0)

10+ Year Member



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

4:11 am on Feb 5, 2009 (gmt 0)

5+ Year Member



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

2:09 pm on Feb 5, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

3:03 pm on Feb 8, 2009 (gmt 0)

5+ Year Member



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

3:48 pm on Feb 8, 2009 (gmt 0)

5+ Year Member



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

5:40 pm on Feb 10, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month