Django sum of a particular field for some distinct column

Question

I have a table OrderTransaction which has foreign key for table Order. I want to calculate the outstanding amount i.e. the amount for which transaction has not yet occurred. To calculate that I first need to calculate the total amount (Order table has field total amount) so that I can subtract the amount of transaction that has taken place from it. I am grouping by the the query by recorded_by field because I need to know what salesman has collected how much amount. Following is my query.

order_transaction_qs
.exclude(recorded_by=None)
.order_by("recorded_by")
.values("recorded_by")
.annotate(
    cash_in_hand=Coalesce(
        Sum("amount", filter=Q(payment_method=PaymentMethod.CASH_ON_DELIVERY)), Value(0)
    ),
    cheque=Coalesce(
        Sum("amount", filter=Q(payment_method=PaymentMethod.CHEQUE)), Value(0)
    ),
    others=Coalesce(
        Sum(
            "amount",
            filter=~Q(
                payment_method__in=[
                    PaymentMethod.CHEQUE,
                    PaymentMethod.CASH_ON_DELIVERY,
                ]
            ),
        ),
        Value(0),
    ),
    order_amount=Sum(
        "order__total_amount"
    ),  # NOTE: Multiple transactions on same orders will give extra amount.
    outstanding=ExpressionWrapper(
        F("order_amount") - (F("cash_in_hand") + F("cheque") + F("others")),
        output_field=FloatField(),
    ),
)

The problem with above query is that if there are multiple transaction for same order, it is adding the total_amount multiple times. Please suggest me what to do.

Elizabeth Bradley · Accepted Answer · 2022-07-26 19:43:06Z

0

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries according to the django docs: https://docs.djangoproject.com/en/3.2/topics/db/aggregation/#combining-multiple-aggregations

This post shares a solution to a similar problem: Django multiple annotate with Sum get wrong answer

answered Jul 26, 2022 at 19:43

Elizabeth Bradley

7511 gold badge6 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django sum of a particular field for some distinct column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related