131

I have a model

class ItemPrice( models.Model ):
     price = models.DecimalField ( max_digits = 8, decimal_places=2 )
     ....

I tried this to calculate the sum of price in this queryset:

items = ItemPrice.objects.all().annotate(Sum('price'))

what's wrong in this query? or is there any other way to calculate the Sum of price column?

I know this can be done by using for loop on queryset but i need an elegant solution.

Thanks!

Ahsan
  • 10,980
  • 12
  • 48
  • 78
  • Does this answer your question? [Django SUM Query?](https://stackoverflow.com/questions/6481279/django-sum-query) – Flimm Aug 06 '20 at 13:42

6 Answers6

272

You're probably looking for aggregate

from django.db.models import Sum

ItemPrice.objects.aggregate(Sum('price'))
# returns {'price__sum': 1000} for example
Flimm
  • 115,689
  • 38
  • 227
  • 240
MattH
  • 35,418
  • 10
  • 81
  • 84
52

Use .aggregate(Sum('column'))['column__sum'] reefer my example below

sum = Sale.objects.filter(type='Flour').aggregate(Sum('column'))['column__sum']
ugali soft
  • 2,493
  • 25
  • 25
44

Annotate adds a field to results:

>> Order.objects.annotate(total_price=Sum('price'))
<QuerySet [<Order: L-555>, <Order: L-222>]>

>> orders.first().total_price
Decimal('340.00')

Aggregate returns a dict with asked result:

>> Order.objects.aggregate(total_price=Sum('price'))
{'total_price': Decimal('1260.00')}
Ibrohim Ermatov
  • 1,899
  • 16
  • 13
8

Using cProfile profiler, I find that in my development environment, it is more efficient (faster) to sum the values of a list than to aggregate using Sum(). eg:

sum_a = sum([item.column for item in queryset]) # Definitely takes more memory.
sum_b = queryset.aggregate(Sum('column')).get('column__sum') # Takes about 20% more time.

I tested this in different contexts and it seems like using aggregate takes always longer to produce the same result. Although I suspect there might be advantages memory-wise to use it instead of summing a list.

UncleSaam
  • 176
  • 1
  • 7
  • 5
    Alternatively, use a generator expression instead of a list: `sum_a = sum(item.column for item in queryset)`. The only difference is the removed `[]`s. This saves the memory space for calculating the entire list before `sum()` iterates over it. – Code-Apprentice Oct 08 '20 at 17:28
  • getting an error : 'decimal.Decimal' object is not iterable – Roni X Dec 13 '20 at 19:26
3

Previous answers are pretty well, also, you may get that total with a line of vanilla code...

items = ItemPrice.objects.all()
total_price = sum(items.values_list('price', flat=True))
Jcc.Sanabria
  • 438
  • 1
  • 8
  • 21
0

YOU COULD ALSO GET THE SUM BY THIS WAY:

def total_sale(self):
    total = Sale.objects.aggregate(TOTAL = Sum('amount'))['TOTAL']
    return total

REPLACE THE 'amount' WITH THE COLUMN NAME FROM YOUR MODEL YOU WANT TO CALCULATE THE SUM OF and REPLACE Sale WITH YOUR MODEL NAME.

Frank Fayia Kendemah
  • 59
  • 1
  • 2