0

I have a table that contains a set of bills. Each bill is categorized. Like CreditCard, Loan, and Personal...

Im trying to get the total amount for category.

class Debt(models.Model):
    Categories = [
    ('Credit Card', 'Credit Card'),
    ('Mortgage', 'Mortgage'),
    ('Loan', 'Loan'),
    ('Household Bill', 'Household Bill'),
    ('Social', 'Social'),
    ('Personal', 'Personal')

]
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    creditor = models.ForeignKey(Creditor, on_delete=models.CASCADE)
    amount = models.FloatField(blank=True,null=True)
    category = models.CharField(max_length=50, blank=True,null=True,

I think that using annotate is the correct approach, but I'm can't work out how to group the categories together to get the total.

all_outgoing = Debt.objects.annotate(total=Sum('amount')).filter(category='personal')

I don't think I should need to create a annotate for each category, but maybe I do? Is there a better way?

Is the better way to wrap in a loop and pass the category onto the filter? Thanks

JacksWastedLife
  • 254
  • 2
  • 15

1 Answers1

0

The correct method for this situation is aggregate, not annotate. See this: Difference between Django's annotate and aggregate methods?

That said, this stub of code creates a dictionary that contains the total for each category:

dict = {}
for cat in Debt.Categories: 
    dict[cat[0]]==Poll.Debt.filter(category=cat[0]).aggregate(Sum('amount'))

It should work, but I haven't tested it completely.

StefanoTrv
  • 199
  • 7