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