# Classify by Average after Sum

**URL:** <https://forum.djangoproject.com/t/classify-by-average-after-sum/8662>\
**Category:** Using Django\
**Created:** [July 7, 2021, 1:49pm UTC](https://forum.djangoproject.com/t/classify-by-average-after-sum/8662 "2021-07-07T13:49:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![LiorA1](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/liora1/32/4101_2.png) [@LiorA1](https://forum.djangoproject.com/u/LiorA1)\
**Post date:** [July 7, 2021, 1:49pm UTC](https://forum.djangoproject.com/t/classify-by-average-after-sum/8662/1 "2021-07-07T13:49:20Z")

</div>

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:

```auto
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 ?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 7, 2021, 5:13pm UTC](https://forum.djangoproject.com/t/classify-by-average-after-sum/8662/2 "2021-07-07T17:13:28Z")

</div>

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](https://docs.djangoproject.com/en/3.2/ref/models/expressions/#using-aggregates-within-a-subquery-expression). I think this is pretty close to what you’re trying to do.

---

<div class="post-metadata">

**Author:** ![LiorA1](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/liora1/32/4101_2.png) [@LiorA1](https://forum.djangoproject.com/u/LiorA1)\
**Post date:** [July 9, 2021, 1:35pm UTC](https://forum.djangoproject.com/t/classify-by-average-after-sum/8662/3 "2021-07-09T13:35:12Z")

</div>

I think I figure it out:

```auto
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.