How to get the sum of transactions per month in django

Question

I'm building a transaction application and need to display the data in charts using chart.js. I don't know how to get the sum for each month of the year.

my model

class Transaction(models.Model):
    trans_id = models.CharField(max_length=100, primary_key=True)  
    trans_date = models.DateTimeField(auto_now_add=True)  
    group_id = models.ForeignKey('Group', on_delete=models.CASCADE)  
    username = models.ForeignKey(User, on_delete=models.CASCADE)  
    trans_amount = models.FloatField()
    trans_type = models.CharField(max_length=20, default="contribution")  

    def __str__(self):
        return str(self.trans_id)

How can I get the sum totals?

Answer 1

You can make use of the Trunc database function

Give this a try

from django.db.models import Sum
from django.db.models.functions import TruncMonth

Transaction.objects.annotate(month=TruncMonth("trans_date"))
    .values("month")
    .annotate(sum=Sum("trans_amount"))

The reslut will look like

[
    {
        "month": "2022-04-01T00:00:00Z",
        "sum": 10.0
    },
    {
        "month": "2022-05-01T00:00:00Z",
        "sum": 20.0
    }
]