Skip to content
Advertisement

How to sum with condition in a Django queryset

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