id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34551	Case-When aggregation over aggregated fields doesn't work since 4.2	Denis Roldán	nobody	"This same QuerySet was working on 4.1.X and prior versions and stopped working on 4.2.X:


{{{
annotated_users  = users_qs.annotate(
            total_things=Subquery(
                OtherModel.objects.filter(user_id=OuterRef(""pk"")).annotate(
                    total_objs=F(""total"")
                ).values(""total_objs"")
            )
)

annotated_users.aggregate(
            sum_total_objs=Sum(""total_things""),
            avg_conversion_rate=Case(
                When(
                    sum_total_objs=0,
                    then=0,
                ),
                default=Round(
                    (Sum(""sum_total_confirmed_objs"") / Sum(""sum_total_objs"")) * 100, 2
                ),
                output_field=FloatField(),
            )
)
}}}

As you can see `sum_total_objs` is an aggregated field that is also used on a second field to calculate the conversion rate. To avoid a zero division problem, we were using a Case-When clause over that field. It works well on any 4.1 and prior versions but stopped working since 4.2, raising a `FieldError` like: 

`Cannot resolve keyword 'sum_total_objs' into field`

The bug is reproducible with an extra test on the django aggregation test suite:


{{{
    def test_referenced_group_by_aggregation_over_annotation(self):
        total_books_qs = (
            Book.objects.filter(authors__pk=OuterRef(""pk""))
            .order_by()
            .values(""pk"")
            .annotate(total=Count(""pk""))
            .values(""total"")
        )
        
        annotated_authors = Author.objects.annotate(
            total_books=Subquery(total_books_qs.annotate(
                    total_books=F(""total"")
            ).values(""total_books"")),
            total_books_a=Subquery(total_books_qs.filter(
                name__istartswith=""a""
            ).annotate(
                    total_books_a=F(""total"")
            ).values(""total_books_a"")),
        ).values(
            ""pk"",
            ""total_books"",
            ""total_books_a"",
        ).order_by(""-total_books"")
        
        totals = annotated_authors.aggregate(
            sum_total_books=Sum(""total_books""),
            sum_total_books_a=Sum(""total_books_a""),
            a_over_total_rate=Case(
                When(
                    sum_total_books=0,
                    then=0,
                ),
                default=Round(
                    (Sum(""total_books_a"") / Sum(""total_books"")) * 100, 2
                ),
                output_field=FloatField(),
            ),
        )
        
        self.assertEqual(totals['sum_total_books'], 3)
        self.assertEqual(totals['sum_total_books_a'], 0)
        self.assertEqual(totals['a_over_total_rate'], 0)

}}}


Thanks for the support!"	Bug	closed	Database layer (models, ORM)	4.2	Normal	needsinfo	orm, aggregate, case, when, field error, bug		Unreviewed	0	0	0	0	0	0