This message was deleted.

I haven't tried your case specifically .. have you tried different versions of syntax that accomplish the same thing, such as:

having SUM("mt_attach_attempts")<>0

?

Well, I tried

HAVING "kpi_case1" <> 0

and it works ; but also filters results with 0 (not only NULL)

yesterday I was re-reading druid expressions and noticed this one. Try it dropping the HAVING

safe_divide(SUM("mt_attach_failures")*1.0, SUM("mt_attach_attempts"))

safe_divide(x,y) returns the division of x by y if y is not equal to 0. In case y is 0 it returns 0 or null if druid.generic.useDefaultValueForNull=false

https://druid.apache.org/docs/latest/misc/math-expr.html maybe it wont work as it’s an aggregation, but let’s see

Daniel, your safe_divide() idea works for me (against the wikipedia demo dataset), although I still have to use it within a Having clause, e.g.:

having safe_divide(SUM("mt_attach_failures")*1.0, SUM("mt_attach_attempts")) is not null

Oliver, a correction to my having clause (I can't update my post above), would check both numerator and denominator, e.g.:

having SUM("mt_attach_attempts")<>0 and SUM("mt_attach_failures")<>0

that should logically check the same thing as your CASE statement.

Thanks a lot for your answers, that's great ! It won't work for me: I also need the formula to be somehow "standard SQL" (because it is used to query on another DB and safe_divide is not supported). But I found a workaround for that: I replace NULL with 'NaN' when sending the query to Druid. The formula sent to Druid is now

(Case When SUM("mt_attach_attempts")<>0 THEN (CAST(SUM("mt_attach_failures") AS DOUBLE)/CAST(SUM("mt_attach_attempts") AS DOUBLE)) ELSE 'NaN' END) AS "kpi_case1",

and I adapted the filter

HAVING "kpi_case1" IS NOT NULL AND CAST("kpi_case1" AS varchar) <> 'NaN'

. And now it works for me. 🙂

another note — if you like standard SQL then you may like setting

druid.generic.useDefaultValueForNull = false

in druid's server properties