1

I'm trying to perform a query in django that is equivalent to this:

SELECT SUM(quantity * price) from Sales GROUP BY date.

My django query looks like this:

Sales.objects.values('date').annotate(total_sum=Sum('price * quantity'))

The above one throws error:

Cannot resolve keyword 'price * quantity' into field

Then I came up with another query after checking this https://stackoverflow.com/a/18220269/12113049

Sales.objects.values('date').annotate(total_sum=Sum('price', field='price*quantity'))

Unfortunately, this is not helping me much. It gives me SUM(price) GROUP BY date instead of SUM(quantity*price) GROUP BY date.

How do I query this in django?

Eranki
  • 750
  • 1
  • 11
  • 30

1 Answers1

1

You should be using F expressions to perform operations on fields:

from django.db.models import F

Sales.objects.values('date').annotate(total_sum=Sum(F('price') * F('quantity')))

Edit: assuming that price is a DecimalField and quantity is a IntegerField (of different types) you would need to specify output_field in Sum:

from django.db.models import DecimalField, F

Sales.objects.values('date').annotate(total_sum=Sum(F('price') * F('quantity'), output_field=DecimalField()))
Abdul Aziz Barkat
  • 19,475
  • 3
  • 20
  • 33
  • In my case I have to add output_field: Sales.objects.values('date').annotate(total_sum=Sum(F('price')*F('quantity'), output_field=FloatField())). This returns empty query set. – Eranki Mar 07 '21 at 07:34
  • @MaruthiEranki didn't think what your field types were, edited. – Abdul Aziz Barkat Mar 07 '21 at 07:38
  • Sorry for that. I made output_field as FloatField. Issue now is that, django query is not returning any query set. Though I have data. – Eranki Mar 07 '21 at 07:43
  • @MaruthiEranki Are you sure you are using the proper variable, etc.? Or using the proper database? Try directly printing the queryset: `print(Sales.objects.values('date').annotate(.....))` – Abdul Aziz Barkat Mar 07 '21 at 07:45
  • Yes, if I do Sum(F('price')), then it is returning set. Only when I multiply F('price') * F('quantity') it is returning empty set. Don't know what exactly the issue is. – Eranki Mar 07 '21 at 08:32