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

by

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!

More Related Contents:

  1. Convert Django Model object to dict with all of the fields intact
  2. What’s the difference between select_related and prefetch_related in Django ORM?
  3. Django Aggregation: Summation of Multiplication of two fields
  4. How to create an object for a Django model with a many to many field?
  5. Django self-referential foreign key
  6. Django orm get latest for each group
  7. LEFT JOIN Django ORM
  8. Serializing Foreign Key objects in Django
  9. Django Blob Model Field
  10. Direct assignment to the forward side of a many-to-many set is prohibited. Use emails_for_help.set() instead
  11. How to perform OR condition in django queryset?
  12. What is the default order of a list returned from a Django filter call?
  13. Execute code when Django starts ONCE only?
  14. Programmatically saving image to Django ImageField
  15. Does SQLAlchemy have an equivalent of Django’s get_or_create?
  16. Custom Filter in Django Admin on Django 1.3 or below
  17. Pandas GroupBy and select rows with the minimum value in a specific column
  18. Django: How to manage development and production settings?
  19. Django Admin nested inline
  20. django 1.4 how to automatically get user’s timezone from client
  21. Django class-based view: How do I pass additional parameters to the as_view method?
  22. Accessing dictionary by key in Django template
  23. Accessing the user’s request in a post_save signal
  24. Updating context data in FormView form_valid method?
  25. How can I set a default value for a field in a Django model?
  26. Detect whether Celery is Available/Running
  27. How to assign a local file to the FileField in Django?
  28. Why is my static CSS not working in Django?
  29. Writable nested serializer in django-rest-framework?
  30. How to get number of groups in a groupby object in pandas?

Leave a Comment