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
Use Django's count() QuerySet method — simply append count() to the end of the appropriate QuerySet. Generate an aggregate over the QuerySet — Aggregation is when you "retrieve values that are derived by summarizing or aggregating a collection of objects." Ref: Django Aggregation Documentation.
You can either use Python's len() or use the count() method on any queryset depending on your requirements. Also note, using len() will evaluate the queryset so it's always feasible to use the provided count() method. You should also go through the QuerySet API Documentation for more information.
Aggregate calculates values for the entire queryset. Annotate calculates summary values for each item in the queryset.
In Django, above argument is called field lookups argument, the field lookups argument's format should be fieldname__lookuptype=value. Please note the double underscore ( __ ) between the field name(depe_desc) and lookup type keyword contains.
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With