How to achive SUM aggregate() + distinct(fields) in Django ORM

Question

I have a model:

class A(models.Model):
    name = models.CharField()
    price = models.IntegerField()

I need to aggregate Sum of all records in db but before it I want to remove duplicates by name (distinct)

So i try use

 A.objects.all().distinct('name').aggregate(total=Sum('price')

But I got error

NotImplementedError: aggregate() + distinct(fields) not implemented.

So, How can I achieve same result in django orm?

Example of table:
| name | price |
  Nick | 100 |
  Eva  | 200 |
  Nick | 100 |

I want to select all unigue values by name and than Sum price. The result is a simple number. 300 in this example.

Check: https://stackoverflow.com/questions/4371550/django-and-aggregate-sum-of-distinct-values You can use DISTINCT on aggregate functions https://docs.djangoproject.com/en/3.0/ref/models/querysets/#sum — JuanMi Gabarron, Oct 16 '20 at 20:13
@JuanMiGabarron: this means you are summing up *distinct* prices, not summing up prices of distinct *names*. — Willem Van Onsem, Oct 16 '20 at 20:17

score 2 · Answer 1 · answered Oct 28 '20 at 00:55

no i dont try but if ur table -A- like this :

| name | price |
  Nick | 100 |
  Nick | 100 |
  Nick | 100 |
  Eva  | 200 |
  Eva  | 200 |
  Nick | 100 |

the queryset : A.objects.all().values('name').annotate(total=Sum('price')) will give u dict like this :

[{'Nick':400},{'Eva':400}]

try use F() objects:

from django.db.models import Sum, F

A.objects.all().values('name').annotate(total=Sum(F('price')))

score 0 · Answer 2 · answered Oct 16 '20 at 21:03

0

You are probably using sqlite as db backend which does not support distinct on fields The Query

A.objects.all().distinct('name')

would have raised a

NotSupportedError('DISTINCT ON fields is not supported by this database backend')

exception anyway

answered Oct 16 '20 at 21:03

Ismael

score 0 · Answer 3 · answered Oct 27 '20 at 00:42

0

 A.objects.all().values('name').annotate(total=Sum('price'))

try

answered Oct 27 '20 at 00:42

Salim Fh

Do you try your answer? NotImplementedError: annotate() + distinct(fields) not implemented. – Yura Bysaha Oct 27 '20 at 12:09

3 Answers3