QuerySet Aggregate on Group by

I would like to do an average on a subquery with group by, how should I do this in Django without using raw SQL?

See SQL example:

    SELECT date, SUM(calories)
    FROM products_eaten
    GROUP BY date
 ) daily_calories

I already have the subquery (QuerySet) working with values and annotate. Adding aggregate after this fails.

Thanks in advance!

See Aggregation | Django documentation | Django

It’s going to be something like:


(Making assumptions about what the model and column names are based on the original query.)

Hi Ken,
Thanks for your answer. It is possible to eat multiple products per day. I have your query but with a sum to calculate the daily calories. So:
Day 1: 2000 calories (product 1 : 200 kcal, product 2 : 1800kcal)
Day 2: 3000 calories (product 1 : 1200 kcal, product 2 : 1800kcal)

Average over all days : 2500 (this one I struggle with)

I want the average for all days. So first calculating the sum per day and then perform the average of these.

Since the average for all days is simply the total number of calories divided by the number of days:
ProductsEaten.objects.aggregate(avg_daily_calories=Sum('calories')/Count('date', distinct=True))
(Note, assuming that calories is an integer field and not a float or decimal field, you would want to cast that to a float or decimal before doing the division - unless fractional values don’t matter.)

Thanks Ken, I think you are right! I was thinking wrongly that the weighting might result in a different average. :blush: