[FIXED] Struggling with aggregate and subtraction in Django and PostgreSQL

Issue

So I am having an issue with querys (getting the sum of an item(aggregate) and subtracting.

What I am trying to do is 10 (soldfor) - 2 (paid) - 2 (shipcost) = 6

The issue is, if I add another (soldfor) (paid) or (shipcost) = it will add all of them up so the profit becomes double. Another example, If I have an item with the (paid) listed at 3.56 and another item with the same (paid) int, it subtracts the two from each new row. I have tried two queries and I cannot get them to work.

What I get: 10 (soldfor) - 2 (paid) - 2 (shipcost) = 12, because two fields have the same exact input.

So basically, if the two fields have the same number it adds or subtracts them to every row that have the same field with the same number.

models.py

class Inventory(models.Model):
    product = models.CharField(max_length=50)
    description = models.CharField(max_length=250)
    paid = models.DecimalField(null=True, max_digits=5, decimal_places=2)
    bin = models.CharField(max_length=4)
    listdate = models.DateField(null=True, blank=True)
    listprice = models.DecimalField(null=True, max_digits=5, decimal_places=2, blank=True)
    solddate = models.DateField(null=True, blank=True)
    soldprice = models.DecimalField(null=True, max_digits=5, decimal_places=2, blank=True)
    shipdate = models.DateField(null=True, blank=True)
    shipcost = models.DecimalField(null=True, max_digits=5, decimal_places=2, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateField(auto_now=True)

    def __str__(self):
        return self.product + "\n" + self.description + "\n" + self.paid + self.bin + "\n" + self.listdate + "\n" + self.listprice + "\n" + self.solddate + "\n" + self.soldprice + "\n" + self.shipdate + "\n" + self.shipcost

    @property
    def Calculate_profit(self):
        soldfor = Inventory.objects.filter(soldprice=self.soldprice).aggregate(Sum('soldprice'))['soldprice__sum'] or 0.00 
        paidfor = Inventory.objects.filter(paid=self.paid).aggregate(Sum('paid'))['paid__sum'] or 0.00 
        shipfor = Inventory.objects.filter(shipcost=self.shipcost).aggregate(Sum('shipcost'))['shipcost__sum'] or 0.00

        totalprofit = soldfor - paidfor - shipfor

        return totalprofit

and views.py

@login_required(login_url="/login")
def profitsperitem(request):
    inventory = Inventory.objects.filter(soldprice__isnull = False, shipcost__isnull = False).order_by('id')

    return render(request, 'portal/profitsperitem.html', {"inventory": inventory})

I have also tried just doing this query in views.py

  totals = Inventory.objects.aggregate(
            Sum('paid'),
            Sum('soldprice'),
            Sum('shipcost')
            )

    totalprofit = totals['soldprice__sum'] - totals['paid__sum'] - totals['shipcost__sum']

    return render(request, 'portal/profitsperitem.html', {"inventory": inventory, "totalprofit": totalprofit})

How do I get it to where it is just and only for the item and not other items that might have the same (soldfor) (paid) or (shipcost) fields?

Solution

Given I understand what you try to do, you can just work with:

class Inventory(models.Model):
    # …

    def __str__(self):
        return f'{self.product}\n{self.description}\n{self.paid}{self.bin}\n{self.listdate}\n{self.listprice}\n{self.solddate}\n{self.soldprice}\n{self.shipdate}\n{self.shipcost}'

    @property
    def profit(self):
        return self.soldprice - self.paidfor - self.shipcost

Since the fields can be NULLable (making it more complicated), you might have to work with default values:

class Inventory(models.Model):
    # …

    @property
    def profit(self):
        return (self.soldprice or 0.00) - (self.paidfor or 0.00) - (self.shipcost or 0.00)

But using 0.00 as "fallback value" is a bit strange if it is NULL: NULL usually means "unknown", not zero. So in that case if one of the fields is None, we return None:

class Inventory(models.Model):
    # …

    @property
    def profit(self):
        if self.soldprice is not None and self.paidfor is not None and self.shipcost is not None:
            return self.soldprice - self.paidfor - self.shipcost

Answered By - Willem Van Onsem