Link to home
Start Free TrialLog in
Avatar of MayoorPatel
MayoorPatel

asked on

How to Avoid Divide by Zero in a Calculated Column

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!
ASKER CERTIFIED SOLUTION
Avatar of TimCottee
TimCottee
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MayoorPatel
MayoorPatel

ASKER

Hi there I forgot to add that there was this in the from clause

FROM GetCardtypes(3) ct inner join
tblTransactions t
on ct.cardtypeID=t.cardTypeID
inner join tblUsers u on
      u.PHID = t.IssuedToID

Can anyone help add this to the solutions you have provided.