pythonsqldjangodjango-modelsaggregate

How to write a Case() SQL expression with an aggregated field (Sum) using Django's query builder


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.

Note:

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.


Solution

  • 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