Knowledge Drop

# Why is my percent or average measure showing zero (or rounding to an integer) aka where are my decimals?

• 0 replies
• 761 views

Userlevel 5
• Looker Staff
• 0 replies

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.