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.

score 7 · Accepted Answer · edited May 23 '17 at 12:31

7

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'))

edited May 23 '17 at 12:31

Community

1
1

answered May 08 '11 at 16:05

solartic

4,249
3
25
26

I created a new field: date_sold_date = models.DateField(). I set this to just the date part of date_sold. However, when I did values_list('date_sold_date__month') it returned Cannot resolve keyword 'not_after_date__month' into filed. Got the same thing with just values_list('month') too. Looks like I missed something? – ErnieP May 08 '11 at 16:59
What you should have is values_list('date_sold_date') – solartic May 08 '11 at 17:03
Thanks. Got further. Now getting Count not defined when running from django extensions shell. – ErnieP May 08 '11 at 17:09
from django.db.models import Count – solartic May 08 '11 at 17:15
Looks good! Final question (I hope:-)) how do I get the totals for each month into a list e.g., [1, 6, 7, 4, ...] – ErnieP May 08 '11 at 17:21
What does your resultset look like? – solartic May 08 '11 at 17:28
[{'total_item': 1, 'date_sold_date': datetime.date(2010, 2, 19)}, {'total_item': 2, 'date_sold_date': datetime.date(2010, 2, 20)}, {'total_item': 1, 'date_sold_date': datetime.date(2010, 3, 4)}, {'total_item': 1, 'date_sold_date': datetime.date(2010, 5, 25)}, {'total_item': 1, 'date_sold_date': datetime.date(2010, 6, 23)}, {'total_item': 1, 'date_sold_date': datetime.date(2010, 6, 24)}, {'total_item': 1, 'date_sold_date': datetime.date(2010, 6, 25)}, {'total_item': 2, 'date_sold_date': datetime.date(2010, 6, 26)}, {'total_item': 5, 'date_sold_date': datetime.date(2010, 7, 9)}, ] >>> – ErnieP May 08 '11 at 17:35
I'm not sure of a Django way to do this, but you can use python list Comprehensions. [row['total_item'] for row in sale_resultset]. sale_resultset being the result you got from the Django query. – solartic May 08 '11 at 17:47
I guess my problem is it's total by day not month. Have I got to create a new field for date_sold_month in my model to get totals by month or can I just create a different filter and still use date_sold_date? – ErnieP May 08 '11 at 17:53
You could set date_sold_date day value to the 1st for each record. – solartic May 08 '11 at 18:07
Also you can use the third example that I gave for the answer. You can used "strftime('%m', date_sold)" instead of "MONTH(date_sold)" which should work for Sqlite. Though you would have to change this to whatever postgresql equivalent when you make the switch - "to_char(date_sold, 'month')" I guess. – solartic May 08 '11 at 18:14
Some versions of Postgres Might run error code because MONTH() wont exist instead use EXTRACT(MONTH FROM due_date) – Eddwin Paz Nov 30 '17 at 22:25

score 1 · Answer 2 · answered May 08 '11 at 10:56

1

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]

answered May 08 '11 at 10:56

Mikhail Korobov

21,908
8
73
65

1

Thanks. I'd also be interested to see how it would it be done using the standard bundled Django libs. – ErnieP May 08 '11 at 12:05

Get total for each month using Django

2 Answers2