Django and Aggregate: Sum of distinct values?

Question

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.

score 21 · Answer 1 · edited May 23 '17 at 12:34

21

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'))

edited May 23 '17 at 12:34

Community

1
1

answered Dec 06 '10 at 22:34

Jordan Reiter

20,467
11
95
161

Relevant documentation: http://docs.djangoproject.com/en/dev/topics/db/aggregation/ – Jordan Reiter Dec 06 '10 at 22:36
2

The reason why this works is because [`.annotate()` works slightly differently after a `.values()`](https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values): "However, when a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause." – mgalgs Feb 06 '18 at 19:44
1

this way you do not get `DISTINCT` and distinction is achieved by another column in `GROUP BY`. – Sławomir Lenart Sep 02 '19 at 18:11

score 13 · Answer 2 · answered Jan 20 '19 at 15:49

13

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')

answered Jan 20 '19 at 15:49

Thomas Parslow

5,712
4
26
33

4

fine, except the case where the same amount comes from different sources – Sławomir Lenart Sep 02 '19 at 18:28

score 10 · Accepted Answer · answered Dec 17 '19 at 09:46

10

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

answered Dec 17 '19 at 09:46

gabbork

116
1
5

NKSM · Answer 4 · 2021-05-10T20:59:10.627

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.

Brialliant answer. This is the most underrated answer for the question. I wonder why this doesn't get enough upvotes — Mohammed Shareef C, Aug 23 '21 at 06:45

score 4 · Answer 5 · edited Jul 10 '18 at 09:04

4

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')

edited Jul 10 '18 at 09:04

Eric Darchis

24,537
4
28
49

answered Sep 11 '16 at 12:24

arjunattam

2,679
18
24

1

The Github for django-pg-utils is not available anymore and the latest update was in 2017. It still works but beware with this module. – Eric Darchis Jul 10 '18 at 11:19

Nache · Answer 6 · 2020-04-29T11:42:46.520

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

score 1 · Answer 7 · answered Jan 15 '21 at 22:52

1

For older version of Django use Func

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

answered Jan 15 '21 at 22:52

sun_jara

1,736
12
20

This works as long as you have different values(amount). Once you have the same values, it won't work anymore. – NKSM May 10 '21 at 20:16

score 0 · Answer 8 · answered Feb 12 '21 at 15:37

0

You can do this:

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

answered Feb 12 '21 at 15:37

sdil

41
1
5

Django and Aggregate: Sum of distinct values?

8 Answers8

Linked