Escher Escher - 1 year ago 114
Python Question

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

I'm trying to calculate the equivalent of

. Here's a simplified analogy:

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

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
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(commission= ( F('selling_price')-F('cost_price') )\
* sp.commission_rate )

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?

Answer Source

The Sum() aggregate function is available in django.db.models.

from django.db.models import F, Sum

    (F('selling_price') - F('cost_price')) * F('salesperson__commission_rate')

Of course, replace sales__ by your actual related name, according to your models definition.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download