CODEWITHSUNDEEP
djangogroup-bydjango-annotate
Eranki
Eranki

Reputation: 807

annotate group by in django

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?

Upvotes: 1

Views: 152

Answers (1)

Abdul Aziz Barkat
Abdul Aziz Barkat

Reputation: 21807

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()))

Upvotes: 1

Related Questions