How to use combination of annotate and aggregate sum in Django ORM

Question

from the below table I need the output has,

[(Apple,21.0), (Orange,12.0) ,(Grapes,15.0) ]

basically fruits grouped with the sum of their cost

date in (dd/mm//yyyy)

Fruits Table
date        item    price
01/01/2021  Apple   5.0
01/01/2021  Orange  2.0
01/01/2021  Grapes  3.0
01/02/2021  Apple   7.0
01/02/2021  Orange  4.0
01/02/2021  Grapes  5.0
01/03/2021  Apple   9.0
01/03/2021  Orange  6.0
01/03/2021  Grapes  7.0
...........
....

models.py

 class Fruits(models.Model):
        item = models.CharField(max_length=32)
        date = models.DateField()
        price = models.FloatField()

I tried the below code its not working as expected

fruit_prices = Fruits.objects.filter(date__gte=quarter_start_date,date__lte=quarter_end_date)
               .aggregate(Sum('price')).annotate('item').values('item','price').distinct()

The filtering also looks odd: quarter_start_date is used twice? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jan 3, 2022 at 11:38

willeM_ Van Onsem · Accepted Answer · 2022-01-03 11:54:59Z

1

You can work with a GROUP BY with:

from django.db.models import Sum

Fruits.objects.filter(
    date__range=(quarter_start_date, quarter_end_date)
).values('item').annotate(
    total=Sum('price')
).order_by('item')

This will generate a queryset that looks like:

<QuerySet [
    {'item': 'Apple', 'total': 21.0},
    {'item': 'Grapes', 'total': 15.0},
    {'item': 'Orange', 'total': 12.0}
]>

a collection of dictionaries where the keys 'item' and total map to the item and the sum of all the prices for that item that satisfy the given datetime range.

I would however advise to make a FruitItem model and work with a ForeignKey, to convert your database modeling to the Third Normal Form [wiki].

edited Jan 3, 2022 at 11:54

answered Jan 3, 2022 at 11:47

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

massu1000 Over a year ago

instead of order_by('item') it shouldn't be annotate ??

willeM_ Van Onsem Over a year ago

@SivaPerumal: yes. The total should be in the .annotate(..) clause. Forgot that .values(..) does not work with aggregates. Updated.

Collectives™ on Stack Overflow

How to use combination of annotate and aggregate sum in Django ORM

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related