CODEWITHSUNDEEP
djangodjango-models
Chetan Badgujar
Chetan Badgujar

Reputation: 55

How to get Sum with group by in Django query

I am trying to get Sum of my quantity with the group by product_id but it returns multiple records.

I am working on Django and trying to get sum with group by so. I try with annotate and values.

productinventory_ds = (
                ProductInventory.objects.filter(product_id=product_id)
                .values("product_id")
                .annotate(
                    instock_quantity_s=Sum("instock_quantity"),
                    delivered_quantity_s=Sum("delivered_quantity"),
                )
            )

But instead of giving one product id and sum of all the quantity. this query returns multiple results with the same product id.

Upvotes: 1

Views: 586

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476547

You should add a .order_by(..) to force grouping, like:

productinventory_ds = ProductInventory.objects.filter(
    product_id=product_id
).values('product_id').annotate(
    instock_quantity_s=Sum('instock_quantity'),
    delivered_quantity_s=Sum('delivered_quantity')
).order_by('product_id')

Here however it looks like you want to extract the columns for a single product_id. In that case, .aggregate(..) makes more sense:

productinventory_ds = ProductInventory.objects.filter(
    product_id=product_id
).aggregate(
    instock_quantity_s=Sum('instock_quantity'),
    delivered_quantity_s=Sum('delivered_quantity')
)

This will return a dictionary that contains two items with as keys 'instock_quantity_s' and 'delivered_quantity_s'.

Upvotes: 3

Related Questions