Django GROUB BY ... COUNT/SUM Django ORM equivalent


Example

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

Let our model be:

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

GROUP BY ... COUNT:

  • Lets assume that we want to count how many book objects per distinct author exist in our Books table:

    result = Books.objects.values('author')
                          .order_by('author')
                          .annotate(count=Count('author'))
    
  • Now result contains a queryset with two columns: author and count:

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

GROUB BY ... SUM:

  • Lets assume that we want to sum the price of all the books per distinct author that exist in our Books table:

     result = Books.objects.values('author')
                           .order_by('author')
                           .annotate(total_price=Sum('price'))
    
  • Now result contains a queryset with two columns: author and total_price:

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