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
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;