SQL Divide Zero Error In SELECT Statement
I am new to SQL and can use hand. I am getting division by zero error with the following in the SELECT statement:
SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money)) - SUM(Cast((replace(replace(replace (p.[Total Cost], '$', ''), '(','-'), ')','')) as money)) / SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money)) as new_bal
I know I need to use CASE, but I'm not sure about the application.
source to share
You can use NULLIF in the following:
NULLIF(expression1, 0)
In your case, it would be:
SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money)) - SUM(Cast((replace(replace(replace (p.[Total Cost], '$', ''), '(','-'), ')','')) as money)) / NULLIF(SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money)),0) as new_bal
source to share
Try this part here:
SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money))
one in a select statement from your table. You should find that it sometimes returns zeros (or maybe NULLs
). You can further debug it by pulling in SUM
and CAST
out to see what replacements you might be missing.
If it turns out that you haven't missed anything (sometimes the sum is zero), here's how you can use the case statement:
CASE
WHEN SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money)) != 0
THEN SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money)) - SUM(Cast((replace(replace(replace (p.[Total Cost], '$', ''), '(','-'), ')','')) as money)) / SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'), ')','')) as money))
ELSE 0
END as new_bal
As an aside, avoid using spaces or other special characters in column names. I believe the general standard for MySQL will be the same as total_sales
.
source to share