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

by

106👍

✅

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!

👤John Moutafis

0👍

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

👤Prashan Basantia

Leave a comment