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

Prologue:

This is a question arising often in SO:

• Django Models Group By
• Django equivalent for count and group by
• How to query as GROUP BY in django?
• How to use the ORM for the equivalent of a SQL count, group and join query?

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 

2 Answers

We can perform a GROUP BY ... COUNT or a GROUP BY ... SUM SQL equivalent queries on Django ORM, with the use of annotate(), values(), the django.db.models's Count and Sum methods respectfully and optionally the order_by() method:

• GROUP BY ... COUNT:

   from django.db.models import Count result = Books.objects.values('author') .order_by('author') .annotate(count=Count('author')) 

  Now result contains a dictionary with two keys: author and count:

   author | count ------------|------- OneAuthor | 5 OtherAuthor | 2 ... | ... 

• GROUP BY ... SUM:

   from django.db.models import Sum result = Books.objects.values('author') .order_by('author') .annotate(total_price=Sum('price')) 

  Now result contains a dictionary with two columns: author and total_price:

   author | total_price ------------|------------- OneAuthor | 100.35 OtherAuthor | 50.00 ... | ... 

UPDATE 13/04/2021

As @dgw points out in the comments, in the case that the model uses a meta option to order rows (ex. ordering), the order_by() clause is paramount for the success of the aggregation!

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