Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1443
  • 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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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