CODEWITHSUNDEEP
pythondjangodjango-queryset
Maifee Ul Asad
Maifee Ul Asad

Reputation: 4607

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)

Upvotes: 1

Views: 1173

Answers (3)

Bernhard Vallant
Bernhard Vallant

Reputation: 50776

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

Upvotes: 3

Elias Amha
Elias Amha

Reputation: 349

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.

Upvotes: 0

dkamins
dkamins

Reputation: 21918

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)

Upvotes: 0

Related Questions