Django and Aggregate: Sum of distinct values?

I am trying to do a django aggregate function, but开发者_开发百科 am unable to produce the desired result.

What I've got:

income_posts.values_list('category__name','amount')
[(u'Donation', Decimal("2000.00")), (u'Paycheck', Decimal("1200.00")), (u'Donation', Decimal("1000.00"))]

Desired result:

[(u'Donation', Decimal("3000.00")), (u'Paycheck', Decimal("1200.00))]

I need to Sum the 'amount' fields that have the same category__name.

---

From this answer for a related question:

from django.db.models import Sum
income_posts.values('category__name').order_by('category__name').annotate(total=Sum('amount'))

---

Just to add to arjun27's answer. Since that package seems to have been abandoned you might want to just copy past the 3 lines you need from it:

from django.db.models import Sum
class DistinctSum(Sum):
    function = "SUM"
    template = "%(function)s(DISTINCT %(expressions)s)"

Which can be used the same as above:

income_posts.annotate(total=DistinctSum('amount')

---

Django 3.0 introduced "distinct=True" on Sum and Avg: https://docs.djangoproject.com/en/3.0/ref/models/querysets/#sum

---

I think this issue also related to Combining multiple aggregations.

Here is the ticket for this bug.

We can use Subquery(Django Docs) to achieve this issue:

from django.db.models import Subquery, OuterRef, IntegerField, Sum, Value, Count

MyModel.objects.annotate(
    count_model_a=Count('ModelA', distinct=True), 
    sum_model_b=Coalesce(
        Subquery(
            ModelB.objects.filter(
                MyModel=OuterRef('pk')
            ).values('MyModel_id').annotate(
                my_sum=Sum('MyModel_Field')
            ).values('my_sum')[:1],
            output_field=IntegerField()
        ),
        Value(0)
    )
).values("count_model_a", "sum_model_b")

I also used Coalesce(Django Docs) function to prevent returning None.

The above code will run one query to DB.

---

If you are on Postgres, you can use the django-pg-utils package for sum of distinct values.

from pg_utils import DistinctSum
income_posts.annotate(total=DistinctSum('amount')

---

For those who are using django 2.2 LTE, this behavior can be achieved just reproducing django 3.0 commit that implements distinct for Sum:

https://github.com/django/django/commit/5f24e7158e1d5a7e40fa0ae270639f6a171bb18e

this way:

from django.db.models Sum

class SumDistinctHACK(Sum):
    allow_distinct = True

and now you can use the django 3.0 syntax:

queryset.annotate(
    sum_result=SumDistinctHACK(
        'relatedmodel__values_to_sum',
        distinct=True,
    )
)

remember to replace SumDistinctHACK to Sum if you upgrade to django >= 3.0

---

For older version of Django use Func

queryset.annotate(
   sum_result=Sum(
        Func(F('amount'), function='DISTINCT')
   )
)

---

You can do this:

income_posts.values("category__name").distinct().annotate(total=Sum("amount"))