django aggregation: sum then average

Question

Using django's ORM annotate() and/or aggregate(): I want to sum up based on one category field and then average over the category values per date. I tried to do it using two annotate() statements but got a FieldError.

I'm doing this:

queryset1 = self.data.values('date', 'category').annotate(sum_for_field=Sum('category'))

Which outputs a ValuesQuerySet object with things like (so a sum for each value of category):

[{'category': 'apples', 'date': '2015-10-12', sum_for_field=2000},
 {'category': 'carrots', 'date': '2015-10-12', sum_for_field=5000},
 {'category': 'apples', 'date': '2015-10-13', sum_for_field=3000},
 {'category': 'carrots', 'date': '2015-10-13', sum_for_field=6000}, ...
]

I then want to average the sum_for_field field for each date to output something like:

[ {'date': '2015-10-12', avg_final: 3500},
{'date': '2015-10-13', avg_final: 4500}, ...
]

I tried doing this:

queryset2 = queryset1.values('date', 'sum_for_field')
result = queryset2.annotate(avg_final=Avg('sum_for_field'))

But I got this FieldError:

FieldError: FieldError: Cannot compute Avg('sum_for_field'): 'sum_for_field' is an aggregate

Answer 1

Aggregate annotation by group from many aggregate annotations by group is generally a complicated question, but Avg from Sum is a special much easier case.

Expression Avg('sum_for_field') can be evaluated as Sum('sum_for_field') / Count('category', distinct=True) that can be evaluated by Aggregate() expressions. The Sum('sum_for_field') equals Sum('amount').

Solution: (Expected names: The model is Data that has fields date, category, amount.)

qs = Data.objects.values('date').annotate(
    avg_final=Sum('amount') / Count('category', distinct=True)
)

(I'm convinced that very similar questions would be without any solution by current Django 1.11, even with Subquery class, without using a strange extra() method and without raw SQL)