count and group by date
Posted: Mon Sep 01, 2008 3:58 am
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,
wich give me this result
maybe is because my date_aux is DATETIME that why i dont have the same date, how can i avoid this ?
thanks in advance
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`
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'
(...)
thanks in advance