aggregation usage in django queries

It is an easy tutorial to make complex queries in django. I personally believe that every word has a specific meaning to it in the world of technology.

Aggregation:

The meaning of aggregation is "the collection of related items of content so that they can be displayed or linked to".

Cases when Aggregation is used:

1. To find "maximum", "minimum" value of column(field in django) in database table in django terms a model.
2. To find "count" of records in database table based on a column/field value.
3. To find "average" value of a group of similar objects
4. To find "sum" of values of a column of a table/model in database.
5. In most of the cases we use aggregation on columns of data type "integer", "float", "date", "datetime" etc.

Consider  below django model to learn queries that use aggregation

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()

Usage of "Avg" in django queryset

• Find average price across all books?

  from django.db.models import Avg
  out = Book.objects.aggregate(Avg('price'))
  # value of out is something like {'price__avg': 34.35}
  

• Find average price across all books which contains "django" in  name?

  from django.db.models import Avg
  queryset = Book.objects.filter(name__icontains='django')
  out = queryset.aggregate(Avg('price'))
  # value of out is something like {'price__avg': 54.25}
  

• Find average price across all books which has minimum price of 14?

  from django.db.models import Avg
  queryset = Book.objects.filter(price__gte=14)
  out = queryset.aggregate(Avg('price'))
  # value of out is something like {'price__avg': 25.65}
  

Usage of "Max" in django queryset

• Find maximum price across all books?

  from django.db.models import Max
  out = Book.objects.aggregate(Max('price'))
  # value of out is something like {'price__max': 81.20}
  

• Find maximum price across all books which contains "django" in name?

  from django.db.models import Max
  queryset = Book.objects.filter(name__icontains='django')
  out = queryset.aggregate(Max('price'))
  # value of out is something like {'price__max': 54.25} 
  

Usage of "Min" in django queryset

• Find minimum price across all books?

  from django.db.models import Min
  out = Book.objects.aggregate(Min('price'))
  # value of out is something like {'price__min': 81.20}
  

• Find minimum price across all books which contains "django" in name?

  from django.db.models import Min
  queryset = Book.objects.filter(name__icontains='django')
  out = queryset.aggregate(Min('price'))
  # value of out is something like {'price__min': 54.25} 
  

Usage of "SUM" in django queryset

• Find sum of  prices all books?

  from django.db.models import Sum
  out = Book.objects.aggregate(Sum('price'))
  # value of out is something like {'price__sum': 81.20} 

• find sum of all prices of books which contains "django" in name
  

  from django.db.models import Min
  queryset = Book.objects.filter(name__icontains='django')
  out = queryset.aggregate(Sum('price'))
  # value of out is something like {'price__sum': 54.25}
  

Usage of "COUNT" in django queryset

• Find count of  all books?

  from django.db.models import Count
  out = Book.objects.aggregate(Count('rating'))
  # value of out is something like {'rating__count': 8} 

• find count of all prices of books which contains "django" in name
  

  from django.db.models import Count
  queryset = Book.objects.filter(name__icontains='django')
  out = queryset.aggregate(Sum('price'))
  # value of out is something like {'rating__count': 5}
  

Multiple aggregations in a single query

from django.db.models import Avg, Max, Min
Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
# value of out is something like
#  { 'price__avg': 34.35, 'price__max': 81.20, 'price__min': 12.99}

These are the very basic queries that used aggregation. we can also use aggregation on "foreign key" fields, "many-to-many" fields and we can also use it with joins("__" notation in django).

 aggregation Avg Count databases Django Max Min models optimization queryset Sum