I am trying to sum Django query with a condition. Suppose I have got some data like this:
| Name | Type | --------------- | a | x | | b | z | | c | x | | d | x | | e | y | | f | x | | g | x | | h | y | | i | x | | j | x | | k | x | | l | x |
And these types are string and they have values, like x = 1, y = 25, z = -3
How can I sum up all the values without a loop? Currently using a loop.
data = A.objects.all() sum = 0 mapp = {'x': 1, 'y': 25, 'z': -3} for datum in list(data): sum = sum + mapp[datum.type] print(sum)
Advertisement
Answer
To perform the calculation inside the database use Queryset.aggregate()
with an aggregate expression that uses Case
/When
:
from django.db.models import Sum, Case, When A.objects.all().aggregate( amount=Sum(Case( When(type="x", then=1), When(type="y", then=25), When(type="z", then=-3), default=0, output_field=FloatField() )) )