Django: Calculate the Sum of the column values through query

Question

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!

Erickson

Does this answer your question? Django SUM Query? – Willable

Answer 1

You're probably looking for aggregate

from django.db.models import Sum

ItemPrice.objects.aggregate(Sum('price'))
# returns {'price__sum': 1000} for example

Preclinical

How can i get total count whose price=5000 ? – Dolorous

Remember returns dictionary not float/integer e.g. {'price__sum':1000} . Can get float/integer with yourdict['price__sum'] – Drizzle

Answer 2

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

sum = Sale.objects.filter(type='Flour').aggregate(Sum('column'))['column__sum']

Palisade

I am getting an error : 'decimal.Decimal' object is not iterable – Elizabetelizabeth

Answer 3

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

Pelmas

I appreciate you showing how to specify the key to store the sum value into. – Idaidae

Answer 4

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.

Parade

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. – Monandry

getting an error : 'decimal.Decimal' object is not iterable – Elizabetelizabeth

Answer 5

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

Sarcous

Answer 6

You could also get the sum 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.

Maleficence

Please don't forget to import Sum: from django.db.models import Sum – Maleficence

Answer 7

You need to use aggregate() and Sum() to calculate the sum of price column as shown below. *The query with all() is equivalent to the query without all() as shown below:

from django.db.models import Sum

print(ItemPrice.objects.all().aggregate(Sum('price')))
print(ItemPrice.objects.aggregate(Sum('price')))

Then, these dictionaries below are outputted on console:

{'price__sum': Decimal('150.00')}
{'price__sum': Decimal('150.00')}

And, you can change the default key price__sum to priceSum for price column as shown below:

from django.db.models import Sum
                                        # ↓ Here
print(ItemPrice.objects.all().aggregate(priceSum=Sum('price')))
print(ItemPrice.objects.aggregate(priceSum=Sum('price')))
                                  # ↑ Here

Then, the default key is changed as shown below:

{'priceSum': Decimal('150.00')}
{'priceSum': Decimal('150.00')}

Blisse