homepage Welcome to WebmasterWorld Guest from 50.19.206.49
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Making a query that counts grouped by day
ntbgl




msg:3839481
 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




msg:3840703
 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




msg:3842707
 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




msg:3842941
 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




msg:3845003
 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




msg:3845020
 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




msg:3846529
 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