# Django ORM: get the monthly average of price for each category

## Issue

Imagine this simple model:

``````class Expense(models.Model):
price = models.DecimalField(decimal_places=2, max_digits=6)
description = models.CharField(max_length=300)
category = models.CharField(choices=ExpenseCategory.choices, max_length=20)
created_at = models.DateField()
``````

I’m trying to get the monthly average of `price` for each `category` in the current year. My general thought was to do something like:

``````sub = (
Expense.objects.filter(created_at__year=date.today().year)
.annotate(month=TruncMonth("created_at"))
.values("month", "category")
.annotate(total=Sum("price"))
.order_by("month")
)
qs = Expense.objects.values("category").annotate(avg=Avg(Subquery(sub.values("total"))))
``````

I’m basically trying to:

1. Truncate the months of `created_at`
2. Group by `category` and `month`
3. Sum the `prices`
4. Aggregate the `prices` for each `category`

It works just fine if I do like:

``````for category in categories:
sub.filter(category=category).aggregate(avg=Avg("total"))
``````

## Solution

Your query can be more simple than you think. Your current attempt at the solution is:

1. Truncate `created_at` to get the month
2. Group by `category` and `month`
3. Sum `prices`
4. Take Avg of the sum for each category

The problem with this is taking an aggregate of an aggregate. Let’s think of your problem in reverse (We will do a bit of mathematics here). You want the average of the monthly price of a category, if we consider only one category and the monthly prices to be an array `M[12]`, then we can express this as:

``````(M[0] + M[1] + ... + M[11]) / 12
``````

Each of the values in M can be considered to be a summation of `prices` where the month matches. If we consider P[12][] to be a 2 dimensional array containing prices for each month we can rewrite above formula as:

``````(Sum(P[0]) + Sum(P[1] + ... + Sum(P[12])) / 12
``````

Thinking of this further it is simply the sum of all prices in the year divided by 12! That means your query can simply be written as:

``````from django.db.models import ExpressionWrapper, FloatField, Sum, Value

qs = Expense.objects.filter(
created_at__year=date.today().year
).values("category").annotate(
avg=ExpressionWrapper(
Sum("price") / Value(12), output_field=FloatField()
)
)
``````

Note: Dividing by 12 means we are assuming that we have data for the entire year, this would probably not be true for the current year so instead we should divide by the appropriate number of months. We might also want to filter upto the previous month in case we are not significantly into the current month.

Answered By – Abdul Aziz Barkat