Page 1 of 1

count and group by date

Posted: Mon Sep 01, 2008 3:58 am
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

Re: count and group by date

Posted: Mon Sep 01, 2008 4:14 am
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

Re: count and group by date

Posted: Mon Sep 01, 2008 5:27 am
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`)