how i group do this mysql query

Question

i want to make charts system and i think it must be like that

1 jan 2009  = 10 post
2 jan 2009  = 2 post
4 jan 2009  = 10 post
6 jan 2009  = 60 post

and i have posts table that has id,user_id,date how i can select from posts to show it like that

Answer 1

Try ..

SELECT DATE_FORMAT(`date`, '%e %M %Y') as `post_date`, COUNT(*)
FROM your_table
GROUP BY `post_date`

More information to reference:

• DATE_FORMAT
• Aggregate Functions