CODEWITHSUNDEEP
django
nasor
nasor

Reputation: 441

How to get the difference value from table - django?

I am new to Django and came up with a problem. I have a table like

TABLE
Id      Title     Type   Value
1       A         1      10
2       A         1      20
3       A         2      5
4       A         2      8 
5       B         1      1000

Model

class Company(models.Model):
    company             = models.ForeignKey(Company, on_delete=models.CASCADE)
    value               = models.IntegerField() 
    type                = models.SmallIntegerField(default='1')
    date                = models.DateTimeField(default=timezone.now)
    user                = models.ForeignKey(User, on_delete=models.CASCADE)

Now from the above table how can I get a value where I sum the value of a similar type and subtract the value from the different type ((A+A)-(A+A)) = 17). Also, I have a value of two types only.

How can I write the query to get the result.

Upvotes: 2

Views: 44

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476503

You can aggregate over the model with:

from django.db.models import Q, Sum, Value
from django.db.models.functions import Coalesce

Company.objects.aggregate(
    total=(Coalesce(Sum('value', filter=Q(type=1)), Value(0)) -
              Coalesce(Sum('value', filter=Q(type=2)), Value(0)))
)['total']

Upvotes: 4

Related Questions