Redshift Divide By Zero Puzzler

Question

I was getting a divide by 0 error with this code:

CASE
WHEN DENOMINATOR >= 0
THEN SUM(INT1 * INT2 / DENOMINATOR)
ELSE 0
END AS RATIO

However when I changed to the following code, it worked.

CASE
WHEN DENOMINATOR >= 0
THEN SUM(INT1) * INT2 / DENOMINATOR
ELSE 0
END AS RATIO

Could someone help me understand the reason so I can avoid this in the future? BTW, the first sample worked in Vertica. I realize summing just what needs to be summed rather than doing the calculation before the summation is a better programming practice. However still am curious.

Answer 1

I think the best way to avoid divide-by-zero is to use nullif():

SUM(INT1 * INT2 / NULLIF(DENOMINATOR, 0))

or:

SUM(INT1) * INT2 / NULLIF(DENOMINATOR, 0)

This returns NULL, which I find more sensible for a divide-by-zero situation. You can add COALESCE() to get 0, if you like.