homepage Welcome to WebmasterWorld Guest from 50.16.112.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Making a query that counts grouped by day
ntbgl

5+ Year Member



 
Msg#: 3839479 posted 2:38 am on Feb 1, 2009 (gmt 0)

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+ Year Member



 
Msg#: 3839479 posted 10:48 pm on Feb 2, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 3839479 posted 4:11 am on Feb 5, 2009 (gmt 0)

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

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



 
Msg#: 3839479 posted 2:09 pm on Feb 5, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 3839479 posted 3:03 pm on Feb 8, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 3839479 posted 3:48 pm on Feb 8, 2009 (gmt 0)

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

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



 
Msg#: 3839479 posted 5:40 pm on Feb 10, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved