QuerySet Aggregate on Group by

Using Django Using the ORM
1

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 AVG(calories) AS AVG_DAILY_CALORIES
FROM (
    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!

2

See Aggregation | Django documentation | Django

It’s going to be something like:

ProductsEaten.objects.values('date').annotate(avg_daily_calories=Avg('calories'))

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

3

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.

4

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

5

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