SQL Divide By Zero Error In SELECT Statement

Question

I'm brand new to SQL and could use a hand. I'm getting a divide by zero error with the following in my 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 that I need to use CASE, but I'm not sure of the application.

Answer 1

You could use NULLIF in following:

NULLIF(expression1, 0)

In your case It will 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

Answer 2

Try doing this portion here:

SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'),  ')','')) as money))

alone in a select statement from your table. You should find that it's sometimes returning zeroes (or perhaps NULLs). You can further debug it by taking out the SUM and the CAST to find out what replacements you might be missing.

If it turns out you're not missing anything (sometimes the sum is just zero), then here's how you'd use a 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 common standard for MySQL would be smoething like total_sales.