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

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

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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!

Method 2

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating