0

I am wondering if it is possible to do more advanced calculations in a query?

Here I am getting the data fine and want to add the profit which is the Sum(credit) - Sum(debit).

my error: Cannot compute Sum('credit'): 'credit' is an aggregate

trd = Trades.objects.all ().filter ( acct_id = acct.id ) 
      .values ( 'issue' ) 
      .annotate (
        cnt = Count ( 'issue' ),
        debit = Sum ( 'debit' ),
        credit = Sum ( 'credit' ),
        shrs = Sum ( 'shares' ),
        profit = Sum ( 'credit' ) - Sum ( 'debit' )
    ) 
    .order_by ( 'issue' )

Thanks.

diogenes
  • 1,865
  • 3
  • 24
  • 51

1 Answers1

0

Instead of SUM(credit) - SUM(debit), see if you can do the equivalent of profit = SUM('credit'-'debit'). That should give you the result you want, but remove the SUM of a SUM which django is complaining about.

ravioli
  • 3,749
  • 3
  • 14
  • 28
  • strange, profit = Sum('credit' - 'debit') gives me this error: unsupported operand type(s) for -: 'str' and 'str'. if I change to a + it gives me a different error? – diogenes Dec 04 '17 at 02:29
  • Another idea...take a look at the aggregate() function: https://stackoverflow.com/questions/8616343/django-calculate-the-sum-of-the-column-values-through-query . I think that's closer to what you're looking for. – ravioli Dec 04 '17 at 02:30
  • One more: https://stackoverflow.com/questions/12165636/django-aggregation-summation-of-multiplication-of-two-fields – ravioli Dec 04 '17 at 02:37