How to sum with condition in a Django queryset-CodePudding

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)

CodePudding user response：

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

CodePudding user response：

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)

CodePudding user response：

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.