Django - Annotating multiple Sum() object gives wrong result

Question

models.py looks like this

class Channel(Model):
    name = CharField()

class Contract(Model):
    channel = ForeignKey(Channel, related_name='contracts')
    fee = IntegerField()

class ContractPayment(Model):
    contract = ForeignKey(Contract, related_name='payments')
    value = IntegerField()

When I query a model:

Channel.objects.annotate(pay=Sum('contracts__fee'))

It returns: 75000. And Its correct but when I query like this:

Channel.objects.annotate(pay=Sum('contracts__fee'))
               .annotate(paid=Sum('contracts__payments__value'))

And it returns: pay: 96000, paid: 33000. As you can see the pay is changed. What is going on here? I read the ticket #10060 but no luck.

Hopefull in Django 3.0 was added a flag `distinct` for `Sum`. — funnydman, Mar 11 '20 at 07:44
I tried `distinct` but the `Contract` model have so many same value of fees so I can't use `distinct` here. — kyore, Mar 11 '20 at 07:45
Did you see https://stackoverflow.com/questions/56567841/django-count-and-sum-annotations-interfere-with-each-other? — funnydman, Mar 11 '20 at 07:48
@Horatiu Jeflea I was wondering that is it a still bug in Django 3.0. But you have another solution its up to you — kyore, Mar 11 '20 at 07:59
Have you tried to inspect the generated SQL query? What version of django are you using? If this is really a bug, you should open a ticket on django I guess — Lotram, Mar 11 '20 at 09:18

score 0 · Answer 1 · answered Mar 11 '20 at 08:06

0

Use two queries to get the desired result:

Channel.objects.annotate(pay=Sum('contracts__fee'))

and

Channel.objects.annotate(paid=Sum('contracts__payments__value'))

answered Mar 11 '20 at 08:06

Horatiu Jeflea

7,256
6
38
67

What if I want differentiate of `pay` and `paid`. For example: `qs.annotate(diff=F('pay') - F('paid'))` – kyore Mar 11 '20 at 08:14

score 0 · Answer 2 · answered Mar 11 '20 at 10:36

0

I think you have to use distinct=True in annotate() as below...

Channel.objects.annotate(pay=Sum('contracts__fee', distinct=True)).annotate(paid=Sum('contracts__payments__value', distinct=True))

answered Mar 11 '20 at 10:36

MK Patel

1,354
1
7
18

Do you try this? – MK Patel Mar 14 '20 at 13:28
1

I tried it and it won't work because `Contract` and `ContractPayment` models have so many same of fees – kyore Mar 23 '20 at 06:37
Do you got the solution or still finding? – MK Patel Mar 23 '20 at 06:44
1

I achieve this with different query. But this question is still acceptable – kyore Mar 23 '20 at 06:46

score 0 · Answer 3 · answered Mar 11 '20 at 18:21

If you're not using Django3, I think this should work:

from django.db.models import Subquery, OuterRef, Sum
contracts = Contract.objects.filter(channel__id=OuterRef("id")).values("fee")
tot = contracts.values("channel_id").annotate(total=Sum("fee")).values("total")
Channel.objects.annotate(pay=Subquery(tot), paid=Sum('contracts__payments__value')).values()

Django - Annotating multiple Sum() object gives wrong result

3 Answers3