count and group by date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

count and group by date

Post by bouncer »

hi there,

can anyone tell me how can i count the number of rows in a table, in a certain status, and group by date, i.e. i have 100 rows with the same date, and 50 with another date, then i want it to count this rows and group then like on 2008-09-01 i have 100 and on 2008-09-02 i have 50, i hope you understand what i want.

i've tried this,

Code: Select all

 
SELECT `date_aux`, COUNT(`ref`) AS count FROM `content` WHERE `status`='1' GROUP BY `date_aux`
 
wich give me this result

Code: Select all

 
'2008-09-01 10:23:00' '1'
'2008-09-01 15:10:00' '1'
'2008-09-01 10:23:00' '1'
'2008-09-02 09:05:00' '1'
(...)
 
maybe is because my date_aux is DATETIME that why i dont have the same date, how can i avoid this ?

thanks in advance
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Re: count and group by date

Post by bouncer »

i manage to solve this issue,

Code: Select all

 
SELECT `date_aux`, COUNT(`ref`) AS count FROM `content` WHERE `status`='1' GROUP BY [b]LEFT(`date_aux`, 10)[/b]
 
sorry for this useless topic ... :(

regards
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: count and group by date

Post by onion2k »

That's not a very nice solution. It relies on string manipulation ... it'd be more logical to keep everything as a date rather than converting to a string to do grouping. I would use the DATE() function instead:

Code: Select all

SELECT `date_aux`, COUNT(`ref`) AS count FROM `content` WHERE `status`='1' GROUP BY DATE(`date_aux`)
Post Reply