CODEWITHSUNDEEP
pythondjangodjango-orm
Escher
Escher

Reputation: 5766

Is it possible to GROUP BY an aggregate query with django ORM?

I'm trying to calculate the equivalent of SELECT SUM(...) FROM ... GROUP BY .... Here's a simplified analogy:

Let's say Salesperson objects sell stuff and get a commission on the margin they generate through each Sale:

sp = Salesperson.objects.get(pk=1)
my_sales = Sale.objects.filter(fk_salesperson=sp)

#calculate commission owing to sp
commission = 0
for sale in my_sales:
    commission += sp.commission_rate\
                     * (sale.selling_price - sale.cost_price)

That last loop could be done with something like:

.annotate( commission= ( F('selling_price')-F('cost_price') )\
                            * sp.commission_rate )

But can I then further aggregate the query for all Salesperson objects? I.e. I want to know every salesperson's commission (i.e. roughly SELECT SUM( (sale_price-cost_price) * commission_rate) FROM Sales GROUP BY Salesperson). I could do something like below, but I'm trying to do it with ORM:

commissions = []
salespeople = Salesperson.objects.all()
for sp in salespeople:
    data = Sale.objects.filter(fk_salesperson=sp)\
       .annotate(salesperson=F('sp__email')\
       .annotate(commission= ( F('selling_price')-F('cost_price') )\
                                 * sp.commission_rate )
    commissions.append(data)

Is there a way to do this with a single query (making the reporting db server do the work) rather than doing it on my application server?

Upvotes: 3

Views: 561

Answers (1)

Antoine Pinsard
Antoine Pinsard

Reputation: 34922

The Sum() aggregate function is available in django.db.models and you can use related fields in an F expression.

from django.db.models import F, Sum

Sales.objects.values('salesperson__id').annotate(commission=Sum(
    (F('selling_price') - F('cost_price')) * F('salesperson__commission_rate')
))

Upvotes: 2

Related Questions