Getting 'divide by zero' error when numerator, not denominator is 0

1
votes

I have a 'Divide by 0' mystery in Redshift that is NOT coming from the denominator being 0.

I have two columns that are both decimals:Sales is always > 0, and it's my denominator. "Flagged Sales" is between 0 and up to the value of Sales.

When I run the code below (using Postico to query Redshift) I get the correct results, except that when "Flagged Sales"=0 I would like the result to be 0, not NULL. 
SELECT 
   NULLIF("Flagged Sales") / NULLIF(Sales,0)::decimal AS "% of Sales Flagged"
FROM Table1;

The following attempts to get 0 when the numerator is 0 have resulted in the 'Divide by zero' error: 

CASE 
    WHEN (NULLIF("Flagged Sales",0) / NULLIF(Sales,0)::decimal) IS NULL THEN 0
    ELSE (NULLIF("Flagged Sales",0) / NULLIF(Sales,0)::decimal)
    END AS "% of Sales Flagged"

Same with: 

COALESCE((NULLIF("Flagged Sales") / NULLIF(Sales,0)::decimal)) AS "% of Sales Flagged"

Any ideas why this is happening?

1
sqlpostgresqlnullamazon-redshiftdivide-by-zero
Can you narrow down to a small set of data that can reproduce this problem and include it here? - andreamc
When inexplicable errors like this pop up for me, I like to take as much out of the equation as possible. I love Postico, but it is not made by the creators of Postgres, per se. I might suggest that just for this query (while you debug it) that you see if you get the same results from the psql command line. I have had a few cases where the GUI got in my way. Perhaps this is one? - hunteke
Shy of that, I might also suggest that you add a WHERE condition that lets you narrow down your dataset (binary search) to find the offending row(s). - hunteke
can you try where sales >0 and share the result. - Rahul Gupta
@venial-steps please can you let me know whether my code below works for you? - Jon Scott

1 Answers

0
votes

This following code works fine for all combinations of Flagged Sales and Sales

select COALESCE(("Flagged Sales" / NULLIF(Sales,0)),0) AS "% of Sales Flagged"
from (select 0 as "Flagged Sales", 0 as Sales) as a
;