Retrieving content grouped by date

[KevinM1]

Once again, it's time to showcase my ignorance of MySQL.  Yay!

 

I have a few tables filled with different kinds of content.  These tables all have a date_added field, which is a datetime.  What I'd like to do is retrieve content from a table, and order it by date, grouped by month, like the way most blogs do it.  So, something like:

 

August 2010 (5)

September 2010 (11)

October 2010 (2)

 

etc.

 

I think a query like:

 

SELECT COUNT(*), *month and year* FROM content GROUP BY *month and year* DESC

 

Would be what I want, but I'm unsure how to:

 

1. Group by month and year

2. Obtain that month and year

 

Any ideas?  I feel like I'm close, but just need a nudge in the right direction.

[PFMaBiSmAd]

Should be close -

SELECT COUNT(*) as cnt, MONTHNAME(date_added) as mn, YEAR(date_added) an yr FROM content GROUP BY yr, MONTH(date_added) DESC

[JonnoTheDev]

You can add the count in yourself with a left join

SELECT DATE_FORMAT(date_added, '%M %Y') AS fulldate FROM tablename GROUP BY DATE_FORMAT(date_added, '%m-%y') ORDER BY date_added DESC