Django Model Aggregations 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
Bookstable:result = Books.objects.values('author') .order_by('author') .annotate(count=Count('author'))
-
Now
resultcontains a queryset with two columns:authorandcount: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
Bookstable:result = Books.objects.values('author') .order_by('author') .annotate(total_price=Sum('price')) -
Now
resultcontains a queryset with two columns:authorandtotal_price:author | total_price ------------|------------- OneAuthor | 100.35 OtherAuthor | 50.00 ... | ...
