Percentages in Starburst Enterprise IDE

Whenever I do a division of integers on Starburst, the results are always rounded either to 0 or 1. I would like the casting to automatically to set to decimals when this division is done. Additionally I need the DIV0 function to handle divisions by 0.

1 Like

TWO great questions in ONE post! Let’s see if I can help.

First up is the need for decimal places.

select 100 / 3 AS decimal_answer;
returns 3

select cast(100 AS double) / cast(3 AS double) as decimal_answer;
returns 33.333333333333336

select round(cast(100 AS double) / cast(3 AS double), 3) as decimal_answer;
returns 33.333

Second up is the divide by 0.

SELECT 100 / 0 AS div0_answer;
blows up!

SELECT COALESCE(TRY(100 / 0), 0) AS div0_answer;
returns 0

Please let me know if this doesn’t help.

1 Like

Just keep in mind that the result of a division by zero is NOT zero, but rather undefined, or even infinity. As such I suggest to be very careful about using that approach to return zero as the result.