I'm trying to sum an annotated field in Python

First,

class InvoiceItems(models):
  units = FloatField(...)
  price = FloatField(...)
  sku = CharField(...)

So I want to annotate the units * price then Sum() them all up for a given sku but

qs = InvoiceItems.annotate(
  amount=Sum(F('units')*F('price'), output_field=models.FloatField())
)

isn't working.

My actual code:

def customer_purchase_detail(**kwargs):
    from_date = kwargs.get('from_date', False)
    to_date = kwargs.get('to_date', False)
    channel = kwargs.get('channel', False)
    customer = kwargs.get('customer', False)
    sku = kwargs.get('sku', False)

    query_set = Invoice.objects.filter(
        record_type=3,
    )

    if from_date:
        query_set = query_set.filter(date__gte=datetime.datetime.strptime(from_date, '%Y-%m-%d'))

    if to_date:
        query_set = query_set.filter(date__lte=datetime.datetime.strptime(to_date, '%Y-%m-%d'))

    if channel:
        query_set = query_set.filter(order__customer__category=channel)

    if customer:
        query_set = query_set.filter(order__customer__code=customer)

    if sku:
        query_set = query_set.filter(order__orderitem__product__sku=sku)

    query_set = query_set.annotate(
        amount=Sum(
            ((F('order__orderitem__order_quantity') - F('order__orderitem__cancel_quantity')) * F(
                'order__orderitem__unit_price')) - F('order__orderitem__discount_amount'),
            output_field=models.FloatField()
        ),
        quantity=Sum(
            (F('order__orderitem__order_quantity') - F('order__orderitem__cancel_quantity') -
             F('order__orderitem__remove_quantity')) *
            F('order__orderitem__pieces_per_unit'),
            output_field=models.IntegerField()
        )
    )

    query_set = query_set.values(
        'order__orderitem__product__sku',
        'order__orderitem__product__description',
        'order__orderitem__product__description_en',
        'amount',
        'quantity'
    ).order_by('-amount').all()

    total = reduce(lambda acc, x: acc   x['amount'], query_set, 0)

    result = [
        {
            'sku': q['order__orderitem__product__sku'],
            'name': q['order__orderitem__product__description'],
            'name_en': q['order__orderitem__product__description_en'],
            'amount': q['amount'],
            'quantity': q['quantity'],
            'ppu': round((q['amount'] / q['quantity']) if q['quantity'] > 0 else 0),
            'pct': round(q['amount'] / total, 3)
        }
        for q in query_set
    ]

    # Made this a list for JsonResponse() that this query returns to

    return result

If my table is

sku | unit | price
AA | 2 | 1.00
AA | 3 | 2.00
AA | 5 | 2.00
BB | 4 | 1.00

I want the result

sku | amount
AA | 18
BB | 4

but the result set Django is sending out is

sku | amount
AA | 8
AA | 10
BB | 4

So summing and grouping some sets, not others. P.S. I noticed that in the simple example, it works as I have it working with another query. Somehow it doesn't with this one. Any advice would be appreciated! Thanks!

CodePudding user response：

You should group by the sku, so:

qs = InvoiceItems.values('sku').annotate(
  amount=Sum(F('units')*F('price'), output_field=models.FloatField())
).order_by('sku')

This will make a QuerySet of dictionaries, so:

<QuerySet [
    {'sku': 'AA', 'amount': 8},
    {'sku': 'BB', 'amount': 4}
]>

That being said, it might be better to make a Product model, and thus use a ForeignKey to that Product instead of a sku.

CodePudding user response：

I don't know why this works, but I moved my filters

    if from_date:
        query_set = query_set.filter(date__gte=datetime.datetime.strptime(from_date, '%Y-%m-%d'))

    if to_date:
        query_set = query_set.filter(date__lte=datetime.datetime.strptime(to_date, '%Y-%m-%d'))

    if channel:
        query_set = query_set.filter(order__customer__category=channel)

    if customer:
        query_set = query_set.filter(order__customer__code=customer)

    if sku:
        query_set = query_set.filter(order__orderitem__product__sku=sku)

after the annotate and all works now.