How to execute a GROUP BY ... COUNT or SUM in Django ORM?

user_7414939 0
Viewed 40133

Prologue:

This is a question arising often in SO:

I have composed an example on SO Documentation but since the Documentation will get shut down on August 8, 2017, I will follow the suggestion of this widely upvoted and discussed meta answer and transform my example to a self-answered post.

Of course, I would be more than happy to see any different approach as well!!

Question:

Assume the model:

class Books(models.Model):
    title  = models.CharField()
    author = models.CharField()
    price = models.FloatField()

How can I perform the following queries on that model utilizing Django ORM:

  • GROUP BY ... COUNT:

    SELECT author, COUNT(author) AS count
FROM myapp_books GROUP BY author

  • GROUP BY ... SUM:

    SELECT author,  SUM (price) AS total_price
FROM myapp_books GROUP BY author
group-by django-orm django python
2 Answers

in group by SUM() you can get almost two dict objects like

inv_data_tot_paid =Invoice.objects.aggregate(total=Sum('amount', filter=Q(status = True,month = m,created_at__year=y)),paid=Sum('amount', filter=Q(status = True,month = m,created_at__year=y,paid=1)))
print(inv_data_tot_paid)
##output -{'total': 103456, 'paid': None}

do not try out more than two query filter otherwise, you will get error like

user_136053750
Related