TSQL SUM Divide BY Zero Error
Posted on 2008-06-20
I have this script
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
sum(Uservalue39) MsgCount, sum(Uservalue38) emailcount,
sum(Uservalue36) faxcount, sum(Uservalue37) faxudcount,
left join bc_companies on calls_bc_d.companyid=bc_companies.companyid
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
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