tim_carter
asked on
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(Taken Count) AnswerTime,
sum(Talktimetalk) TalkTimeTot,
sum(Talktimetalk)/Sum(Take nCount) 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_co mpanies.co mpanyid
where
thedate > '13-05-2008'
and thedate < '15-06-2008'
group by calls_bc_d.CompanyId,bc_co mpanies.re fno1
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(Taken Count) AnswerTime,
sum(Talktimetalk)/Sum(Take nCount) 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
I have this script
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
SELECT
calls_bc_d.CompanyId,
Sum(TakenCount) Calls,
sum(uservalue05)/Sum(Taken
sum(Talktimetalk) TalkTimeTot,
sum(Talktimetalk)/Sum(Take
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_co
where
thedate > '13-05-2008'
and thedate < '15-06-2008'
group by calls_bc_d.CompanyId,bc_co
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(Taken
sum(Talktimetalk)/Sum(Take
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER