While trying to make the archive pages of this site more useful, I had a bit of trouble working with Django's annotation feature to give me the numbers I was looking for. I wanted to show on the index page how many posts were published in a given month. This ticket was very helpful to figure out how to do it, and as a bonus it means it will work more simply in future-Django :)

At the moment, the workaround is unfortunately database specific. The ticket contains an example using SQLite, here's how I did it using PostgreSQL. I think with MySQL you'd need to play with YEAR() and MONTH() to get the same.

bymonth_select = {"month": """DATE_TRUNC('month', creation_date)"""} # Postgres specific
months = Article.objects.exclude(draft=True).extra(select=bymonth_select).values('month').annotate(num_posts=Count('id')).order_by('-month')

I removed a couple of filters for readability, but this is basically a normal QuerySet with a couple of Django caveats:

1. Write your filters before the annotate call, as the Count() processes only what comes before it
2. .values('blah') for a GROUP BY effect, without it you'll get '1' for every record fetched back
3. .order_by(), with or without an argument otherwise things will likely get messed up by the order_by of your Meta class if any -- inspired by comment #8 of the ticket linked above, thanks!