Get total for each month using Django

Question

I have a model like this:

class Sales(models.Model):
    item = models.CharField(max_length=40)
    date_sold = models.DateTimeField()

I'd like to present a bar chart of total items sold each month for a particular year. My charting software expects a list like the one below where each number is the total items sold for a particular month.

sales_by_month = [4, 6, 7, 3, 5. ...]  

How would I do this?

I've got a feeling I should be using dates, aggregate and/or annotate, but can't work it out.

I'm using SQLite, but plan to go to PostgreSQL.

I did this in another project using ruby on rails and it was pretty straightforward as far as I remember, so I'm hoping Django has a good solution.

Answer 1

Usually for something like this the code below should work.

Sales.objects.filter(date_sold__year='2010').values_list('month').annotate(total_item=Count('item'))

But you have a dateTime field, and not just the month for each item. You could try doing the following, though I doubt it will work.

values_list('date_sold__month')

The other option would be to try using extra() as shown in this thread. You could therefore try something like this if you are using MySql.

Sales.objects.filter(date_sold__year='2010').extra({'month' : "MONTH(date_sold)"}).values_list('month').annotate(total_item=Count('item'))

Answer 2

using django-qsstats-magic:

import qsstats

queryset = Sales.objects.filter(date_sold__year='2010')

stats = qsstats.QuerySetStats(queryset, 'date_sold')

start, end = date(2010, 1, 1), date(2011, 1, 1)
sales_by_month = stats.time_series(start, end, interval='months')

# sales_by_month is a list of 2-element tuples where the first element is
# a datetime object and the second is value

result = [r[1] for r in sales_by_month]