#SUM of COUNT in annotation

7 messages · Page 1 of 1 (latest)

slender hill
#

Hi! 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 have already tried a lot of different things, including subqueries and custom Count functions but can't get it to work. Is there someone that knows how to solve this?

pallid forum
slender hill
#

django.core.exceptions.FieldError: Cannot compute Sum('<CombinedExpression: F(booking_claims__price) / Count(F(booking_claims))>'): '<CombinedExpression: F(booking_claims__price) / Count(F(booking_claims))>' is an aggregate

#

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)

pallid forum
#

What if you move the count into the first annotate so that the sum of only operating on the F expressions

slender hill
#

That resulted in the same error... I found another (somewhat hacky) solution. I added a new field to my Booking model:
booked_for_count = models.PositiveIntegerField(editable=False, default=0, null=False, blank=False)

Then, I use an override of the save function of the ModelForm that I use to create and update bookings to set this additional field as follows:

    def save(self, commit=True):
        self.instance.booked_for_count = self.cleaned_data["booked_for"].count()
        return super(BookingModelForm, self).save(commit)

Now, I am able to use the booked_for_count field in the annotation.

high siren
#

can i use Count / Sum with subquery and if yes so please share an example for each cus some how it works but gives me wrong data
and when i use it with regulare expirations it just working fine !