Redshift - Divide by zero error

2
votes

I am having issues with the below that keeps throwing "Divide by zero" error

Given below is the query I am using:

select cust_name,
sum(case WHEN sale_date > CURRENT_TIMESTAMP-14 AND sale_date < CURRENT_TIMESTAMP then 1 else 0  END) AS "0_2_sales_count",
sum(case WHEN sale_date > CURRENT_TIMESTAMP-28 AND sale_date < CURRENT_TIMESTAMP-14  then 1 else 0  END) AS "2_4_sales_count",
("0_2_sales_count") - ("2_4_sales_count") as "load_volume_diff_0_2_2_4",
(("0_2_sales_count" - "2_4_weeks_load_volume")/"2_4_sales_count"::float) * 100  as "load_volume_diff_percent_0_2_2_4"
from sales 
group by cust_name;

I have zeroesfor different customers in "0_2_sales_count" and "2_4_sales_count" columns. Could anyone advice how could I handle this above error. I have tried using case when column = 0 but still the same problem occurs.

I am using Amazon Redshift DB.

Thanks

3
sqlamazon-redshiftdivide-by-zero
Well, what do you want to happen? Do you want to return 0, or perhaps the number undivided? ie. 10/0, what should that become in your code? 10? 0? - Lasse V. Karlsen
@LasseVågsætherKarlsen, expect it to return a '0' in such a case - dark horse

3 Answers

1
votes

You could check for 0 in "2_4_sales_count" using a case and use the proper code for each situation 

select cust_name,
      sum(case WHEN sale_date > CURRENT_TIMESTAMP-14 
          AND sale_date < CURRENT_TIMESTAMP then 1 else 0  END) AS "0_2_sales_count",
        sum(case WHEN sale_date > CURRENT_TIMESTAMP-28 
          AND sale_date < CURRENT_TIMESTAMP-14  then 1 else 0  END) AS "2_4_sales_count",
      ("0_2_sales_count") - ("2_4_sales_count") as "load_volume_diff_0_2_2_4",
      CASE WHEN "2_4_sales_count"::float = 0
          THEN 0 ELSE (("0_2_sales_count" - "2_4_weeks_load_volume")/"2_4_sales_count"::float) * 100  END as "load_volume_diff_percent_0_2_2_4"
from sales 
group by cust_name;
1
votes

Try with case when like case when "2_4_sales_count"::float =0 then 1 end

select cust_name,
sum(case WHEN sale_date > CURRENT_TIMESTAMP-14 AND sale_date < CURRENT_TIMESTAMP then 1 else 0  END) AS "0_2_sales_count",
sum(case WHEN sale_date > CURRENT_TIMESTAMP-28 AND sale_date < CURRENT_TIMESTAMP-14  then 1 else 0  END) AS "2_4_sales_count",
("0_2_sales_count") - ("2_4_sales_count") as "load_volume_diff_0_2_2_4",
(("0_2_sales_count" - "2_4_weeks_load_volume")/case when "2_4_sales_count"::float =0 then 1 end) * 100  as "load_volume_diff_percent_0_2_2_4"
from sales 
group by cust_name;
0
votes

Presumably, the problem is in this row:

(("0_2_sales_count" -"2_4_weeks_load_volume") / "2_4_sales_count"::float) * 100  as "load_volume_diff_percent_0_2_2_4"

I would write this as:

(("0_2_sales_count" - "2_4_weeks_load_volume") * 100.0 / nullif("2_4_sales_count", 0.0) as "load_volume_diff_percent_0_2_2_4"

The simplest solution for divide-by-zero is nullif().