CODEWITHSUNDEEP
djangodjango-querysetdjango-orm
Serikbol Oralbek
Serikbol Oralbek

Reputation: 33

Getting sum of averages in a django queryset

def get_queryset(self):
    current_academic_year = get_current_academic_year()
    domains = Domain.objects.all()
    start_date = self.request.query_params.get("start_date", current_academic_year.start_date)
    end_date = self.request.query_params.get("end_date",  current_academic_year.end_date)
    queryset = Account.objects.annotate(**{
        f"domain_overall_{domain.uuid}": Avg(
            "assessed_from__accountevaluationresult__calculated_rating",
            filter=Q(
                assessed_from__updated__range=(start_date, end_date)
            ) & Q(assessed_from__domain=domain)
        )
        for domain in domains
    }).annotate(**{f"domains_total": ~query~
    })

There are five domains. Here I got 5 key-value pairs with certain calculated values for each domain. I need to get the sum of those values from the first annotation, but have no idea how to retrieve it in continuous annotations.

Response I need in a json must be like this:

What is the best approach/solution here?

Upvotes: 1

Views: 55

Answers (1)

Dan Yishai
Dan Yishai

Reputation: 772

Because you want to fetch all averages from db anyway, I would calculate the sum of all values in Python, after fetching the data.

First, lets simplify the queryset:

queryset = Account.objects.filter(assessed_from__domain__in=domains).annotate(
    domain=F('assessed_from__domain'),
    average=Avg("assessed_from__accountevaluationresult__calculated_rating",
                filter=Q(assessed_from__updated__range=(start_date, end_date)
).values('domain', 'average')

This can be done using vanilla Python like:

total_sum = sum(d.average for d in queryset)

This can also be done faster using Pandas, like:

df = pd.DataFrame(queryset)
total_sum = df['average'].agg('sum')

With this approach, you can also serialize it to JSON easier:

df = df.append({"domain": "domains_totals", "average": total_sum}, ignore_index=True)
df = df.set_index("domain")
json_str = df['average'].to_json()

Upvotes: 1

Related Questions