# annotation of another annotation duplicate query rule

**URL:** <https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526>\
**Category:** Using the ORM\
**Created:** [December 9, 2022, 2:33pm UTC](https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526 "2022-12-09T14:33:28Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mohammad99k](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mohammad99k/32/11246_2.png) [@Mohammad99k](https://forum.djangoproject.com/u/Mohammad99k)\
**Post date:** [December 9, 2022, 2:33pm UTC](https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526/1 "2022-12-09T14:33:28Z")

</div>

When I use an annotation in another annotation, query is duplicated like bellow:

```auto
groups = StudyGroup.objects.filter(group_filter).select_related('parent').annotate(
        no_members=Count('student', distinct=True),
        point=Sum('student __point__ point', filter=point_filter),
        avg_point=ExpressionWrapper(F('point') / F('no_members'), output_field=FloatField()))

```

In above queryset, `Count('student', distinct=True)` calculate for no\_members and when I use no\_members in avg\_point annotation again query calculate `Count('student', distinct=True)` instead of use no\_members directly.

```auto
COUNT(DISTINCT `users_student`.`user_id`) AS `no_members`,
    SUM(`study_league_point`.`point`) AS `point`,
    (
        SUM(`study_league_point`.`point`) / COUNT(DISTINCT `users_student`.`user_id`)
    ) AS `avg_point`,

```

In above my SQL code `COUNT(DISTINCT `users_student`.`user_id`)` is duplicate (`SUM(`study_league_point`.`point`)` too).

my desired query is like this:

```auto
COUNT(DISTINCT `users_student`.`user_id`) AS `no_members`,
    SUM(`study_league_point`.`point`) AS `point`,
    (
        point / no_members
    ) AS `avg_point`,

```

---

<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:** [December 9, 2022, 3:12pm UTC](https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526/2 "2022-12-09T15:12:26Z")

</div>

I see where you opened a ticket for this issue and was informed that the ORM will not do what you want here.

What I would suggest would be to look at an EXPLAIN on this query to see if these replicated queries are really a problem. If you’re using PostgreSQL, you’ll find that the query optimizer can internally optimize many situations like this. (However, whether or not any real optimization _needs_ to occur can also depend upon the amount of data involved. If you’re talking about a relatively small amount of data, it probably doesn’t matter at all either way.

---

<div class="post-metadata">

**Author:** ![Mohammad99k](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mohammad99k/32/11246_2.png) [@Mohammad99k](https://forum.djangoproject.com/u/Mohammad99k)\
**Post date:** [December 9, 2022, 3:17pm UTC](https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526/3 "2022-12-09T15:17:28Z")

</div>

I think it’s bug in Django in MySQL.  
When I use RawSQL query will be optimize and result will be OK.

---

<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:** [December 9, 2022, 3:19pm UTC](https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526/4 "2022-12-09T15:19:36Z")

</div>

What you need to remember is that SQL is not a procedural language, it’s a declaritive language. The SQL you write is _not_ necessarily what the database engine executes.

It’s only by looking at an EXPLAIN can you _really_ determine what is optimized or not.

It’s also not a bug as this is documented and expected behavior for the construct you are using.

---

<div class="post-metadata">

**Author:** ![Mohammad99k](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mohammad99k/32/11246_2.png) [@Mohammad99k](https://forum.djangoproject.com/u/Mohammad99k)\
**Post date:** [December 9, 2022, 3:53pm UTC](https://forum.djangoproject.com/t/annotation-of-another-annotation-duplicate-query-rule/17526/5 "2022-12-09T15:53:08Z")

</div>

You are right. It’s OK  
Thank you.