How to use Django Subquery inside Annotate and Sum

Question

I am trying to calculate total revenue for a day. Each DailyTotal contains a count of items sold (items_sold) and a Price those items were sold that day (items_price) (every items is sold for the same price all day). That part is working, but now I need to convert multiply that value by the exchange rate for that day/country.

rate = ExchangeRate.objects.filter(  
    date=OuterRef('date'), 
    country=OuterRef('country')))  

calc_with_rate = Sum(F('items_sold') * F('items_price') * Subquery(rate.values('rate')), output_field=FloatField(),)  

results = DailyTotal.objects.filter(**query_filters).annotate(
    revenue=calc_with_rate)

but I get:

unsupported operand type(s) for +=: 'int' and 'NoneType

I assume it is because rate.values('rate') is not returning a an int.. but I can't do

rate.values('rate')[0]

or I get:

This queryset contains a reference to an outer query and may only be used in a subquery.

so I am not sure how to complete this query?

You could do: first annotate the rate you get with the Subquery and then annotate the calculation for items_sold * item_price * subquery_result. Then you can sum it. — Erick Mendonça
– Erick Mendonça, Commented May 22, 2018 at 4:22

bubthegreat · Accepted Answer · 2017-11-09 03:15:30Z

1

It looks like you're trying to += with an int and a NoneType - i.e. one of those two variables is returning empty, and you're trying to add them - since you're not doing it explicitly with a += operand in the code above, I'm assuming it's happening somewhere in the Sum(). Debug what it does there and you'll likely find the issue.

answered Nov 9, 2017 at 3:15

bubthegreat

3011 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

How to use Django Subquery inside Annotate and Sum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related