Django annotate group by month

Question

How to sum price group by month?

I try.

import itertools
qs = Contract.objects.values('created', 'proposal__price')
grouped = itertools.groupby(qs, lambda d: d.get('created').strftime('%Y-%m'))
data = [{'month': month, 'quant': sum(list(this_day))} for month, this_day in grouped]
print(data)

But result is no expected.

I need this similar result

[{'month': '2016-04', 'quant': 8650}, {'month': '2016-05', 'quant': 9050}]

Possible duplicate of [How to group by AND aggregate with Django](http://stackoverflow.com/questions/13403609/how-to-group-by-and-aggregate-with-django) — Shang Wang, May 23 '16 at 19:37
Possible duplicate of [Django: Group by date (day, month, year)](http://stackoverflow.com/questions/8746014/django-group-by-date-day-month-year) — tback, Jul 14 '16 at 16:03

score 1 · Accepted Answer · answered May 23 '16 at 20:32

Your this_day inside sum(list(this_day)) is a dict, so you need to build a list with a list comprehension. Example

>>> import itertools
>>> from django.contrib.auth.models import User
>>> li = User.objects.all().values('date_joined', 'username')
>>> gr = itertools.groupby(li, lambda d: d.get('date_joined').strftime('%Y-%m'))
>>> dt = [{'m': m, 'q': sum([len(x['username']) for x in q])} for m, q in gr]
>>> dt
[{'m': '2005-06', 'q': 11}, {'m': '2006-10', 'q': 22}, 
{'m': '2005-06', 'q': 179}, {'m': '2006-08', 'q': 10}, 
{'m': '2006-09', 'q': 30}, {'m': '2005-06', 'q': 74}, ... ]

Or, for your code, probably something like this

data = [{'month': month, 'quant': sum([x['proposal__price'] for x in this_day])} 
        for month, this_day in grouped]

score 0 · Answer 2 · answered May 23 '16 at 20:50

Start by extracting the month and all your values

from django.db import connection
select = {'month': connection.ops.date_trunc_sql('month', 'created')}
qs = Contract.objects.extra(select=select).values('month').annotate(my_total=Sum('proposal__price'))

Now we can use a function to group by dict keys like so:

from itertools import groupby
from operator import attrgetter
get_y = attrgetter('month')
from collections import defaultdict, Counter
def solve(dataset, group_by_key, sum_value_keys):
    dic = defaultdict(Counter)
    for item in dataset:
        key = item[group_by_key]
        vals = {k:item[k] for k in sum_value_keys}
        dic[key].update(vals)
    return dic

Apply it to your queryset's newly annotated my_total, grouped by month:

solved = solve(qs, 'month', ['my_total'])

And you'll have grouped sums by month (month being a datetime object you can change manipulate to meet your needs):

for i in solved: print(i, ":", solved[i]['my_total'])
>>> datetime.datetime(2015, 9, 1, 0, 0, tzinfo=<UTC>) : 67614.23
>>> datetime.datetime(2015, 1, 1, 0, 0, tzinfo=<UTC>) : 54792.39

Now you can extract those values :)

your code result this error: `return Database.Cursor.execute(self, query, params) django.db.utils.OperationalError: ambiguous column name: created ` — Regis Santos, May 25 '16 at 17:16

score 0 · Answer 3 · answered May 25 '16 at 18:08

My code adapted from @C14L.

import itertools
# from .models import Contract
c = Contract.objects.all().values('created', 'proposal__price')
gr = itertools.groupby(c, lambda d: d.get('created').strftime('%Y-%m'))
dt = [{'month': month, 'quant': sum([x['proposal__price'] for x in quant])} for month, quant in gr]
dt

Thanks.

Django annotate group by month

3 Answers3