How to group articles by month published?

[jswallow]

I'm in the middle of updating a hand-written CMS for my site, which has served me well for the last few years. One thing I've decided would be useful would be to allow the end user to just pick a month/year and then just see that month's articles.

I have a field in a table "Documents" called "DatePublished" which is a DateTime type.

How can I query that table to produce a list like this:-

December 2006 (3)
November 2006 (2)
July 2006 (1)

where the number in brackets is the number of articles published?

I've tried plugging in a number of different questions into Google to try and get me started in the right direction, but I'd like some clues (not necessarily the whole answer) on how to do it, so at least I can learn as I go! :)

[Sekka]

You will need an SQL query similar to this,

SELECT DATE_FORMAT(T1.datetimefield, '%M %Y') as period, COUNT(*) as amount FROM mydbtable AS T1  
GROUP BY DATE_FORMAT(T1.datetimefield, '%M %Y')  
ORDER BY T1.datetimefield ASC

[jswallow]

That's brilliant. I've worked out what I wanted to do from that, and expanded on it to include selecting the month and year parts as well so that I can pull up the appropriate list.

Thanks for your help.