Django: SUM of COUNT in annotations

Question

I have the following simple models:

class Member(models.Model):
    name = models.CharField(max_length=100)


class Booking(models.Model):
    date = models.DateField(default=now)
    price = models.DecimalField(max_digits=7, decimal_places=2)
    organizer = models.ForeignKey(Member, on_delete=models.CASCADE)
    booked_for = models.ManyToManyField(Member, related_name="booking_claims")

Now, in a view, I want to annotate my Members with infomation about the total spendings of a member (i.e. the sum of all prices of bookings where this member is the organizer) and the total claims from this member (i.e. the sum of all proportional costs of bookings where the member takes part in).

Ideally, this would work as follows:

members = Member.objects.annotate(
    total_spending=Sum("booking__price", default=0),
    total_claims=Sum(F("booking_claims__price") / Count("booking_claims")),
)

However, I get an error that Count is an aggregate and I cannot use Sum on an aggregate. I know, there exist some related questions and I have already tried a lot of different things, including subqueries and custom Count functions but I can't get it to work. Is there someone that knows how to solve this?

If I use a subquery e.g. like this:

    subquery = (
        Booking.objects.filter(spent_for=OuterRef("pk"))
        .annotate(
            total_claim=ExpressionWrapper(
                Cast(F("price"), FloatField()) / Count("booked_for"),
                output_field=DecimalField(),
            )
        )
        .values("total_claim")
    )

    members = Member.objects.annotate(
       total_claims=Sum(Subquery(subquery)),
    )

Then total_claims only holds the first value of the Queryset returned by the subquery (however, I want to sum up all the values returned by the subquery)

Django: SUM of COUNT in annotations

0 Answers0