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.

Favouritism

CASE WHEN DENOMINATOR >= 0 .... seems wrong... if you're trying to avoid a divide by 0 error... don't you mean > 0 not >= or just <> 0 if you want to allow negatives? – Marquis

I'm guessing negative denominators should be 0. thus the else so just > not >= – Marquis

The negative number issue is irrelevant to the question. The point is DEMONIMATOR should not be zero. I am trying to understand why one works and the other one gives me an error. – Favouritism

I guess I don't see why it matters that one works and one doesn't if the premise of the question itself is faulty. at no time should you allow a denominator of 0. Thus, why is your case GREATER THAN or EQUAL TO 0. The EQUAL too seems wrong. As to why one worked and one didn't... I'd need to see more of the SQL I don't see how you can sum(int1) and not the rest unless you're grouping by int/denominator; and thereby somehow eliminating the zeros in that case. I'd need a small test case to better understand. – Marquis

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.

Rounding

How to cope with negative values of DENOMINATOR? – Cartelize

@JonScott . . . You would need a CASE to handle them. – Rounding