Django ORM Query to perform simple ORDER BY aggregation

Question

Given this simple table,

class Payment(models.Model):
    paid_date = models.DateField("Paid on")
    amount = models.DecimalField(max_digits=7, decimal_places=2)

I want to produce a table the produces the sum of all expenses by month, such as:

Month	Total
2023/11	200
2023/12	400

Since this requires extracting the year and month from the paid_date field, I tried to do this:

1. Add a new field called year_mo that combines the year and month from the paid_date field.
2. Extract just the year_mo and amount fields.
3. Sum up all of the amount for each unique year_mo field.

Here's an example I tried:

    exp_by_month = Payment.objects \
                          .annotate(year_mo=Trunc('paid_date', 'month')) \
                          .values('year_mo', 'amount') \
                          .annotate(Sum('amount'))

This doesn't appear to perform any aggregation, as there's a row in the result for each for in the Payments table. The added amount__sum field is the same as amount. This seems like it should be so easy, but I've really been struggling.

Note that this can be solved (and already has been by a kind commenter) by adding an additional field that reduces the actual paid_date to always represent the 1st day of the month; now all aggregation can simply be done against this new field.

However, I would prefer it if I didn't have to introduce a new field into the model that required maintenance and is dependent on another field. That feels like something I would do using aggregation in a query, but I can't figure out how.

Answer 1

You can add one more field to you Payments model

class Payment(models.Model):
    year_month = models.DateField()
    paid_date = models.DateField("Paid on")
    amount = models.DecimalField(max_digits=7, decimal_places=2)

When you create model instances, for year_month field, regardless of the actual day of the month, you can provide 1. e.g. the actual payment occurred on May 6th 2024, so your paid_date will be 2024/05/06 but your year_month field will be 2024/05/01.

Then you can aggregate the sum of the payments using year_month field:

Payment.objects.values("year_month").annotate(monthly_payments=Sum("amount"))