How to compute Sum of an aggregate with Django?

Question

I'm currently trying to compute the score of a Survey in SQL side only to be able to order survey by their scores.

My current logic is:

Compute the real coefficient of my Answer
Make the sum of that coeficient for my Question (which can have multiples Answer so I use Sum)
compute the number of points of my whole Survey based on the sum of all Question points (Question.point * sum(Answer.coef)) basically

    Survey.objects.annotate(
            answerresponse__realcoef=models.Case(
                models.When(answerresponse__coef__isnull=True,
                            then=models.F('answerresponse__answer__coef')),
                models.When(answerresponse__coef__isnull=False,
                            then=models.F('answerresponse__coef')),
                output_field=models.FloatField(),
            )
            ).annotate(
                answerresponse__realcoef_sum=models.Sum(
                    models.F('answerresponse__realcoef')
                )
            ).annotate(
                points=models.Sum(
                    models.F('answerresponse__realcoef_sum') *
                    models.F('answerresponse__answer__question__points'),
                    output_field=models.IntegerField()
                ),
                maxpoints=models.Sum('sections__question__points')
            )

The database schema is something like:

Survey > Sections > Questions (points) > Answer (coef) > AnswerResponse (coef override)

and I get the following error:

FieldError: Cannot compute Sum('<CombinedExpression: F(answerresponse__realcoef_sum) * F(answerresponse__answer__question__points)>'): '<CombinedExpression: F(answerresponse__realcoef_sum) * F(answerresponse__answer__question__points)>' is an aggregate

which I understand as

That SQL part was not executed yet so you cannot rely on it

Is it possible to achieve that by keeping in the SQL side only?

score 2 · Answer 1 · answered Jan 30 '20 at 11:19

2

You can try to first annotate the result of answerresponse__realcoef_sum * answerresponse__answer__question__points with expression wrapper:

.annotate(
    total=ExpressionWrapper(
        F('answerresponse__realcoef_sum') * F('answerresponse__answer__question__points'), output_field=IntegerField())
)

And then aggregate the result by SUM:

.annotate(points=models.Sum('total'))

answered Jan 30 '20 at 11:19

Charnel

4,222
2
16
28

thanks for your advice, it didn't work since i was thinking that putting the results into answerresponse__ was storing the value for ONE response, now i understand that is aggregated so i'm trying an approach with 2 requests: one to annotate `AnswerResponse` to get the points per response into a Subquery but i'm not able to use it properly since i have "subquery must return only one column" (i'm trying to make a Sum from a SubQuery ) – Chr0nos Jan 30 '20 at 14:47

score 1 · Answer 2 · answered Jan 31 '20 at 09:21

i was able to find a solution for that: i'm using a subquery to compute the total per survey then annotate into the Survey queryset, the OuterRef method allowed me to have a reference to the parent request pk into the subrequest.

my big error was to think that my annotations were local to my nested field but it was a global aggregation

def mark_bet_scores(surveys_qs: models.QuerySet) -> models.QuerySet:
    """Annotate a bet queryset with the following fields:
    - maxpoints : the amount of possibles points to get on this bet (int)
    - score : the current survey score (from 0 to 100) (float)
    - points : the amount of points gained on this bet (int)
    """
    responses = AnswerResponse.objects \
        .filter(survey__pk=models.OuterRef('pk')) \
        .prefetch_related('answer', 'answer__question') \
        .annotate(
            realcoef=models.Case(
                models.When(coef__isnull=True, then=models.F('answer__coef')),
                models.When(coef__isnull=False, then=models.F('coef'))
            )
        ) \
        .annotate(
            points=models.ExpressionWrapper(
                models.F('realcoef') * models.F('answer__question__points'),
                output_field=models.IntegerField()
            )
        ) \
        .values('survey__pk') \
        .annotate(
            total=models.Sum(models.F('points'))
        ) \
        .values('total')

    # now we need to make the relation between `Survey.answerresponse` and
    # responses
    surveys = surveys_qs \
        .annotate(
            maxpoints=models.Sum('sections__question__points'),
            points=models.Subquery(
                responses,
                output_field=models.IntegerField()
            )
        ) \
        .annotate(
            score=models.Case(
                models.When(maxpoints=0, then=0),
                models.When(maxpoints__gt=0, then=models.ExpressionWrapper(
                    models.F('points') / models.F('maxpoints') * 100,
                    output_field=models.FloatField())
                )
            )
        )
    return surveys

i faced similar problem , can you help me to fix please https://stackoverflow.com/questions/62606945/django-using-if-else-or-while-else-inside-sum-function-with-annotation-cannot/62607325?noredirect=1#comment110716672_62607325 — art_cs, Jun 27 '20 at 08:44

How to compute Sum of an aggregate with Django?

2 Answers2