Getting SUM for each unique fields in Django Query-CodePudding

I have the following table relations.

I want SUM of total_cost from Sales_Transaction for each party_name from the Party table. The Parties have 1:M relation with Sales and Sales has 1:M relation with Sales_Transction.

I tried this query but it returns the sum for all parties:

Party.objects.aggregate(sales=Sum('sales__salestransaction__total_cost'))

This is similar to this simple query:

SalesTransaction.objects.aggregate(sales=Sum('total_cost'))

I want output like this:

| Party Name | Total Purchase |
-----------------------------
|Party 1     | total_cost (calculated form Sales Transaction)
|Party 2     | total_cost (calculated form Sales Transaction)

How can I achieve these results in Django Query?

CodePudding user response：

You can .annotate(…) [Django-doc] the Party objects with:

from django.db.models import Sum

Party.objects.annotate(
    sales=Sum('sales__salestransaction__total_cost')
)

The Party objects that arise from this QuerySet will have an extra attribute sales with the total amount of sales for that Party.