Django: Calculate the Sum of the column values through query
PythonDjangoPython Problem Overview
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!
Python Solutions
Solution 1 - Python
You're probably looking for aggregate
from django.db.models import Sum
ItemPrice.objects.aggregate(Sum('price'))
# returns {'price__sum': 1000} for example
Solution 2 - Python
Use .aggregate(Sum('column'))['column__sum'] reefer my example below
sum = Sale.objects.filter(type='Flour').aggregate(Sum('column'))['column__sum']
Solution 3 - Python
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')}
Solution 4 - Python
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.
Solution 5 - Python
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))
Solution 6 - Python
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.