Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Avoid Divide by Zero in a Calculated Column

Posted on 2007-03-27
6
Medium Priority
?
1,169 Views
Last Modified: 2012-06-22
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!
0
Comment
Question by:MayoorPatel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 672 total points
ID: 18798906
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 664 total points
ID: 18799195
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
 
LVL 11

Assisted Solution

by:Ved Prakash Agrawal
Ved Prakash Agrawal earned 664 total points
ID: 18799208
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
 
LVL 1

Author Comment

by:MayoorPatel
ID: 18800409
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.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question