View the original community article here
Last tested: May 1, 2020
Here's a Help Center article to explain this question.
Explanation
If you divide two integers in SQL, you get an integer. Thus 1/2 = 0.
Workaround
The workaround is to cast one integer to a float. An easy way to do that is to multiply by 1.0.
It is very important that the multiplying by 1.0 happens before the division operation.
Working examples:
1.0 * ${count} / ${sum}
${count} * 1.0 / ${sum}
${count} / ( ${sum} *1.0)
What won't work:
${count} / ${sum} *1.0
1.0 * ($[count}/ ${sum})
Note for Druid Database:
${count} * 1.0 / ${sum} will not work. Put 1.0 to the denominator instead ---> ${count} / (${sum} *1.0)
Redshift Edge Case
Dividing by two SUMs in Redshift seems to default to 4 places of precision, as noted in this Stackoverflow article. The workaround is to cast one of them to a higher numeric datatype. ${sum1}::numeric(19,6)/${sum2}
This also happens to measures of type:average on Redshift when the field being averaged is an integer.
Presto Edge Case
Using 1.0 * ${variable}
still will treat the quotient as a int (mentioned in this Stackoverflow) so casting as a double would resolve this. Something like CAST(${variable} AS DOUBLE)
will hopefully do the trick in treating it as a non-int
This content is subject to limited support.