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

John Moutafis picture John Moutafis · Aug 7, 2017 · Viewed 17k times · Source

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
    

Answer

John Moutafis picture John Moutafis · Aug 7, 2017

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
        ...     |      ...