I have a model in Django and this is how it looks like with fewer fields -

I want to group the rows w.r.t buy_price_per_unit and at the same time I also want to know the total units on sale for that buy_price_per_unit.

So in our case only two distinct buy_price_per_unit are available (9, 10). Hence the query would return only two rows like this -

The one last condition which I have to meet is the query result should be in descending order of buy_price_per_unit.

This is what I have tried so far -

orders = Orders.objects.values('id', 'buy_price_per_unit')\
        .annotate(units=Sum("units"))\
        .order_by("-buy_price_per_unit")\

The response for the query above was -

[
  {
    "id": 13,
    "buy_price_per_unit": 10,
    "units": 1
  },
  {
    "id": 12,
    "buy_price_per_unit": 9,
    "units": 10
  },
  {
    "id": 14,
    "buy_price_per_unit": 9,
    "units": 2
  },
  {
    "id": 15,
    "buy_price_per_unit": 9,
    "units": 1
  }
]

The problem with this response is that even for the same price multiple records are being returned.

CodePudding user response：

This is happening because you have id in .values and based on the underlying query, it is grouping on id and buy_price_per_unit both.

So simply remove id from .values

orders = Orders.objects.values('buy_price_per_unit')\
        .annotate(units=Sum("units"))\
        .order_by("-buy_price_per_unit")\