CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-querysetaverage
yaboinav
yaboinav

Reputation: 121

How to calculate average in Django?

I have Category and Product models below:

class Category(models.Model):
    name = models.CharField(max_length=20)

class Product(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    name = models.CharField(max_length=50)
    price = models.DecimalField(decimal_places=2, max_digits=5)

Questions:

  1. How can I average all the prices in Product model?

  2. How can I average all the prices in Product model category by category?

Upvotes: 11

Views: 29071

Answers (4)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can use aggregate() and annotate() with Avg() to average the prices in Product model and category by category as shown below. *You need to use order_by('pk') with annotate() otherwise values are printed in descending order:

from django.db.models import Avg

# Average the prices in "Product" model
print(
    "<All products>",
    Product.objects.aggregate(Avg('price'))
) 

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name,
        Product.objects.filter(category=obj)
                       .aggregate(Avg('price'))
    )

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name, 
        obj.product_set.aggregate(Avg('price'))
    )

print()

# Average the prices in "Product" model category by category
qs = Category.objects.annotate(Avg('product__price')).order_by('pk')
for obj in qs:
    print(obj.id, obj.name, obj.product__price__avg)

Then, these below are outputted on console:

<All products> {'price__avg': Decimal('20.1578947368421053')}

1 Fruits {'price__avg': Decimal('13.5714285714285714')}
2 Vegetable {'price__avg': Decimal('23.0000000000000000')}
3 Meat {'price__avg': Decimal('24.2500000000000000')}
4 Fish {'price__avg': Decimal('25.3333333333333333')}

1 Fruits {'price__avg': Decimal('13.5714285714285714')}
2 Vegetable {'price__avg': Decimal('23.0000000000000000')}
3 Meat {'price__avg': Decimal('24.2500000000000000')}
4 Fish {'price__avg': Decimal('25.3333333333333333')}

1 Fruits 13.5714285714285714
2 Vegetable 23.0000000000000000
3 Meat 24.2500000000000000
4 Fish 25.3333333333333333

And, you can change the default key price__avg and product__price__avg to priceAvg for price column as shown below:

from django.db.models import Avg

# Average the prices in "Product" model
print(
    "<All products>",
    Product.objects.aggregate(priceAvg=Avg('price'))
)                             # ↑ Here

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name,
        Product.objects.filter(category=obj)
                       .aggregate(priceAvg=Avg('price'))
    )                             # ↑ Here

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name, 
        obj.product_set.aggregate(priceAvg=Avg('price'))
    )                             # ↑ Here

print()

# Average the prices in "Product" model category by category
qs = Category.objects.annotate(priceAvg=Avg('product__price')).order_by('pk')
for obj in qs:                 # ↑ Here
    print(obj.id, obj.name, obj.priceAvg)
                                # ↑ Here

Then, the default key is changed as shown below:

<All products> {'priceAvg': Decimal('20.1578947368421053')}

1 Fruits {'priceAvg': Decimal('13.5714285714285714')}
2 Vegetable {'priceAvg': Decimal('23.0000000000000000')}
3 Meat {'priceAvg': Decimal('24.2500000000000000')}
4 Fish {'priceAvg': Decimal('25.3333333333333333')}

1 Fruits {'priceAvg': Decimal('13.5714285714285714')}
2 Vegetable {'priceAvg': Decimal('23.0000000000000000')}
3 Meat {'priceAvg': Decimal('24.2500000000000000')}
4 Fish {'priceAvg': Decimal('25.3333333333333333')}

1 Fruits 13.5714285714285714
2 Vegetable 23.0000000000000000
3 Meat 24.2500000000000000
4 Fish 25.3333333333333333

Upvotes: 0

Alexandr Ovdienko
Alexandr Ovdienko

Reputation: 106

for category in Category.objects.all():
    stat = Product.objects.filter(category_id=category.id).aggregate(Avg('price'))['price__avg']`

Django Aggregation

Upvotes: 6

Yuri Malheiros
Yuri Malheiros

Reputation: 1410

You can use aggregate and Avg in a django query.

Supposing your model Product field name is price:

from django.db.models import Avg

Product.objects.aggregate(Avg('price'))
Product.objects.aggregate(Avg('price'))

Upvotes: 22

WBAR
WBAR

Reputation: 4984

You simply using values(), aggregate() and function Avg() and F().

Let's assume You have Model like this:

class Member(models.Model):
    nickname = models.CharField(max_length=16, unique=True)

class MatchResult(models.Model):
    member = models.ForeignKey('Member')
    wins = models.IntegerField()
    losses = models.IntegerField()

Now You can aggregate those using this syntax (look at __ double underscore notation):

MatchResult.objects.values(
    'username_id', 
    'username__nickname', 
    'wins',
    'losses'
).aggregate(
    user_id=F('username_id'),
    nickname=F('username__nickname'),
    avg_wins=Avg('wins'),
    avg_losses=Avg('losses')
)

Please refer to documentation.

Hint: Order of annotate and filter clauses is IMPORTANT.

Upvotes: 3

Related Questions