I am trying to recreate this SQL statement using Django's query builder:
CASE
WHEN sum(imps) = 0 THEN NULL
ELSE SUM(total_expenses) / (sum(imps) / 1000)
END as "cpm"
I have tried:
MyModel.objects.annotate(
cpm=Case(
When(
Sum("imps")>0,
then=Sum("total_expenses") / Sum("imps"),
),
default=0,
output_field=FloatField(),
)
)
However it doesn't work because of this TypeError '>' not supported between instances of 'Sum' and 'int'
.
If there was a field lookup that enabled me to do total_imps__sum__gt=0
, I am sure that would solve the problem.
Adding Sum("imps")
as an annotation (total_imps=Sum("imps")
) and doing total_imps__gt=0
works but it is not an ideal solution as I would have to create multiple annotations for fields I won't need.
Use NullIf
instead
MyModel.objects.annotate(
Cast(
Sum("total_expenses") /1.0/ NullIf(Sum("imps"), 0)
output_field=FloatField()
),
)
You can use alias()
in your original query to omit adding additional annotation