10

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.

cjremley
  • 111
  • 1
  • 1
  • 7
  • 2
    `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? – xQbert Oct 10 '17 at 15:34
  • I'm guessing negative denominators should be 0. thus the else so just > not >= – xQbert Oct 10 '17 at 15:45
  • 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. – cjremley Oct 10 '17 at 20:14
  • 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. – xQbert Oct 10 '17 at 21:12

1 Answers1

35

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786