Redshift Divide By Zero Puzzler

sql amazon-redshift vertica divide-by-zero
cjremley picture cjremley · Oct 10, 2017 · Viewed 15.5k times · Source

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

Gordon Linoff picture Gordon Linoff · Oct 10, 2017

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.

Related questions

postgresql - view schema privileges

Is there a query I can run to show currently assigned privileges on a particular schema? i.e. privileges that were assigned like so: GRANT USAGE ON SCHEMA dbo TO MyUser I have tried SELECT * FROM information_schema.usage_privileges; …

Read More
How do I view grants on Redshift

I'd like to view grants on redshifts. I found this view for postgres: CREATE OR REPLACE VIEW view_all_grants AS SELECT use.usename as subject, nsp.nspname as namespace, c.relname as item, c.relkind as type, use2.usename …

Read More
How to get the last day of month in postgres?

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using to_date("act_dt",'YYYYMMDD') AS "act date" …

Read More