# 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!
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Select MaxMonthDate,MaxMonth,PassportsIssued,PassportVerifications,SkillVerifications, Case When PassportsIssued = 0 Then 'No Ratio Available' Else Cast((PassportVerifications + SkillVerifications) / PassportsIssued As Varchar(20)) End As Calculated From
(
SELECT
max(dbo.getmonthdate(t.TransDate)) As MaxMonthDate,
max(dbo.getmonth(t.TransDate)) As MaxMonth,
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
From MyTable) Calculation

For example, doing a secondary select means that you can work with the totals without having to recalculate them.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
like this...

Select MaxMonthDate,MaxMonth,PassportsIssued,PassportVerifications,SkillVerifications
, Case When PassportsIssued = 0 Then 'No Ratio Available'
Else Convert(varchar(30),passportVerifications + (skillverifications * 1.00/passportsissued))
End As Calculated
From
(
SELECT
max(dbo.getmonthdate(t.TransDate)) As MaxMonthDate,
max(dbo.getmonth(t.TransDate)) As MaxMonth,
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
From MyTable) as X
0
Database Consultant/Performance ArchitectCommented:
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,
CASE WHEN t.TransType =1 THEN  CAST((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 varchar(2000) ) ELSE 'No Ratio Available' END AS Calculated
0
Author Commented: