How to sum with condition in a Django queryset

user_10305444 0
Viewed 120

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)
django-queryset django python
3 Answers

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

You could shorthand it like:

sum(mapp.get(o['Type'],0) for o in data)

or simpler if you trust the data to have all valid types:

sum(mapp[o['Type']] for o in data)

(Don't trust the data though)

user_3080660

If the mapping x = 1, y = 25 etc... is coming from another table, you can use some SQL voodoo to let the database handle the summation for you. Other wise you have to (one way or another) loop through all results and sum them up.

You could also theoretically just count the distinct amount of x, y and z in the table and have sum = x*count_of_x + y*count_of_y + z*count_of_z based on how the example is structured.

user_194206820
Related