• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1452
  • Last Modified:

TSQL SUM Divide BY Zero Error

Hi guys

I have this script

SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
SELECT
calls_bc_d.CompanyId,
Sum(TakenCount) Calls,
sum(uservalue05)/Sum(TakenCount) AnswerTime,
sum(Talktimetalk) TalkTimeTot,
sum(Talktimetalk)/Sum(TakenCount) TalkTime,
sum(Uservalue39) MsgCount, sum(Uservalue38) emailcount,
sum(Uservalue36) faxcount, sum(Uservalue37) faxudcount,
sum(Uservalue35) SMScount,
bc_Companies.refno1
from Calls_bc_d
left join bc_companies on calls_bc_d.companyid=bc_companies.companyid
where
thedate > '13-05-2008'
and thedate < '15-06-2008'
group by calls_bc_d.CompanyId,bc_companies.refno1
order by bc_companies.refno1

I have a couple of things to this.. First of i get a Divide by Zero error, and ofcourse because some of the rows has zero value. But how can i escape that? i mean these two lines

sum(uservalue05)/Sum(TakenCount) AnswerTime,
sum(Talktimetalk)/Sum(TakenCount) TalkTime,

They will return a zero if both columns has a zero value and then return the Divide by ZERO Error. Is there a way to do some kind of IF in this statements? second.. is the first select

Sum(TakenCount) Calls,. How can i do that if this is ZERO that it will return atleast 1. If i just do a
Sum(TakenCount+1) Calls ... Then my problem is that it will do it for everything.. And for some strange reason that if i do it on 0, then Sum(TakenCount+1) Calls will return 3.. All help is much appreciated

Thanks
0
tim_carter
Asked:
tim_carter
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
case when sum(TakenCount) = 0 THEN 0 else sum(uservalue05)/Sum(TakenCount) end AnswerTime,
case when Sum(TakenCount) = 0 THEN 0 else sum(Talktimetalk)/Sum(TakenCount) end TalkTime,
0
 
tim_carterAuthor Commented:
as always your the best ;) thanks angellll
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now