LiorA1
#1
Hi,

I’m trying to create a query that will find how much each customer spend in purchases, and then classify the customers by their average purchases sum.

annotate return an error and aggregate return a dictionary.

My initial query was:

```
c=Customer.objects.annotate(
total_sum=Sum(F('salesorder__salesorderitems__quantity')*F('salesorder__salesorderitems__prod_id__unit_price'), output_field=FloatField()))\
.values('id','fname','lname','total_sum').order_by('-total_sum')\
.annotate(level=Case(When(Q(total_sum__gt=Avg('total_sum')) ,then="good"),\
When(Q(total_sum__lte=Avg('total_sum')), then="bad"),\
default="None"))
```

I think I can do it with two DB hits:

One to calculate the Average and one to do the classification, but trying to avoid two DB hits.

Can someone clarify how to achieve my goal ?

I think you need to do the aggregation functions (e.g. Avg) within a subquery.

See the docs and example in Using aggregates within a Subquery expression. I think this is pretty close to what you’re trying to do.

1 Like

LiorA1
#3
I think I figure it out:

```
customers = Customer.objects.filter(id=OuterRef('pk'))
customers_total = customers.annotate(total_sum=Sum(F('salesorder__salesorderitems__quantity')*F('salesorder__salesorderitems__prod_id__unit_price'), output_field=FloatField())).values('total_sum')
c=Customer.objects.annotate(
total_sum=Sum(F('salesorder__salesorderitems__quantity')*F('salesorder__salesorderitems__prod_id__unit_price'), output_field=FloatField()))\
.values('id','fname','lname','total_sum').order_by('-total_sum')\
.annotate(level=Case(When(Q(total_sum__gt=Avg(Subquery(customers_total))) ,then=Value("prefered client")),\
When(Q(total_sum__lte=Avg(Subquery(customers_total))), then=Value("regular client")),\
default=Value("None"), output_field=CharField()))
```

Not really sure, but connection.queries shows only one db hit.

1 Like