Solved

# How to Avoid Divide by Zero in a Calculated Column

Posted on 2007-03-27

I am trying to protect a divide by zero in my last column.

SELECT

max(dbo.getmonthdate(t.TransDate)),

max(dbo.getmonth(t.TransDate)),

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS Calculated

The problem is that

(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)

may sometimes be zero in which case I need to return "No Ratio Available"

Can Anyone help? I understand I may have to use a nested case statement or something!