How to calculate average in Django?

Question

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:

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

score 21 · Answer 1 · edited Jan 25 '23 at 11:32

21

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'))

edited Jan 25 '23 at 11:32

Super Kai - Kazuya Ito

22,221
10
124
129

answered Feb 19 '15 at 13:38

Yuri Malheiros

1,400
10
16

This does the average overall, however I need the average of each member if you understand me? – yaboinav Feb 19 '15 at 13:49
Yes! For example Aragorn has 34 wins and 27 losses. Jack has 2 wins and 18 losses. I need the average win rate for EACH member. – yaboinav Feb 19 '15 at 13:54
I think it is easier to use .values to get the wins and losses and do the calculation manually, it is simple math. – Yuri Malheiros Feb 19 '15 at 14:03

score 6 · Answer 2 · edited Jan 25 '23 at 16:06

6

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

Django Aggregation

edited Jan 25 '23 at 16:06

Super Kai - Kazuya Ito

22,221
10
124
129

answered Apr 29 '18 at 13:32

Alexandr Ovdienko

106
1
4

score 3 · Answer 3 · answered Sep 01 '15 at 21:32

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.

Super Kai - Kazuya Ito · Answer 4 · 2023-01-26T18:04:24.623

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

How to calculate average in Django?

4 Answers4