How to Execute a Group by ... Count or Sum in Django Orm

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

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!

How to query as GROUP BY in django?

If you mean to do aggregation you can use the aggregation features of the ORM:

from django.db.models import Count
result = (Members.objects
    .values('designation')
    .annotate(dcount=Count('designation'))
    .order_by()
)

This results in a query similar to

SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation

and the output would be of the form

[{'designation': 'Salesman', 'dcount': 2}, 
 {'designation': 'Manager', 'dcount': 2}]

If you don't include the order_by(), you may get incorrect results if the default sorting is not what you expect.

If you want to include multiple fields in the results, just add them as arguments to values, for example:

    .values('designation', 'first_name', 'last_name')

References:

• Django documentation: values(), annotate(), and Count
• Django documentation: Aggregation, and in particular the section entitled Interaction with default ordering or order_by()

How to group by and aggregate conditional in Django ORM

I managed ot get it myself. The featur I did not know before is using the filter= parameter in aggregation functions (Sum, Count, etc.)

    qs = Worklog.objects.filter( 
            day__range=[start,end] 
        ).values( 
            'worker__fullname' 
        ).annotate( 
            weekdayAvg=Cast(Coalesce(Sum('effort', filter=(~Q(day__week_day=1) & ~Q(day__week_day=7)))/Count('day', distinct=True, filter=(~Q(day__week_day=1) & ~Q(day__week_day=7)))/60/60, 0), FloatField()),
            weekdayCnt=Cast(Coalesce(Count('day', distinct=True, filter=(~Q(day__week_day=1) & ~Q(day__week_day=7))), 0), FloatField()),
            weekendAvg=Cast(Coalesce(Sum('effort', filter=(Q(day__week_day=1) | Q(day__week_day=7)))/Count('day', distinct=True, filter=(Q(day__week_day=1) | Q(day__week_day=7)))/60/60, 0), FloatField()),
            weekendCnt=Cast(Coalesce(Count('day', distinct=True, filter=(Q(day__week_day=1) | Q(day__week_day=7))), 0), FloatField()),
        ).order_by('weekdayAvg', 'worker__fullname')

Also I added Coalesce(..., 0)as otherwiese the Sum would return None (as per this post)

Use Count with aggregate(Sum) in django ORM, Custom Queryset Fields

You can count the number of distinct primary keys:

from django.db.models import Count, Sum

Invoice.objects.aggregate(
    total_amount=Sum('order__order_items__amount'),
    number_of_invoices=Count('pk', distinct=True)
)

This will return a dictionary with 'total_amount' and 'number_of_invoices' as keys.

How to do SELECT COUNT(*) GROUP BY and ORDER BY in Django?

According to the documentation, you should use:

from django.db.models import Count
Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')

values() : specifies which columns are going to be used to "group by"

Django docs:

"When a values() clause is used to constrain the columns that are
returned in the result set, the method for evaluating annotations is
slightly different. Instead of returning an annotated result for each
result in the original QuerySet, the original results are grouped
according to the unique combinations of the fields specified in the
values() clause"

annotate() : specifies an operation over the grouped values

Django docs:

The second way to generate summary values is to generate an independent summary for each object in a QuerySet. For example, if you
are retrieving a list of books, you may want to know how many authors
contributed to each book. Each Book has a many-to-many relationship
with the Author; we want to summarize this relationship for each book
in the QuerySet.

Per-object summaries can be generated using the annotate() clause.
When an annotate() clause is specified, each object in the QuerySet
will be annotated with the specified values.

The order by clause is self explanatory.

To summarize: you group by, generating a queryset of authors, add the annotation (this will add an extra field to the returned values) and finally, you order them by this value

Refer to https://docs.djangoproject.com/en/dev/topics/db/aggregation/ for more insight

Good to note: if using Count, the value passed to Count does not affect the aggregation, just the name given to the final value. The aggregator groups by unique combinations of the values (as mentioned above), not by the value passed to Count. The following queries are the same:

Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')
Transaction.objects.all().values('actor').annotate(total=Count('id')).order_by('total')

Sum of a column with group by in django

try it, add name for annotate column:

CapitalSheet.objects.filter(
    client_id="X1234"
).values("product").annotate(capital=Sum("capital"))
#                            ^^^^^^^

How to group by AND aggregate with Django

Try this:

Rating.objects.filter(attribute__in=attributes) \
    .values('location') \
    .annotate(score = Sum('score')) \
    .order_by('-score')

---

Related Topics