Easy SQL Divide By Zero Occurring when Syntax Seems Correct

Question

I am using the following syntax:

Case 
  When acdtime != 0 Then sum(CAST(ti_stafftime as DECIMAL)/acdtime)*100 
  Else '0' 
End as MyPercent,

Yet I am still getting this error:

Msg 8134, Level 16, State 1, Line 3 Divide by zero error encountered.

What am I doing wrong here?

My whole query is below:

Select 
logid,
row_date,
sum(acdcalls) as 'total calls',
sum(ti_stafftime) as 'total time staffed',
sum(acdtime) as 'time on the phone',
Case
When acdtime != 0 Then sum(CAST(ti_stafftime as DECIMAL)/acdtime)*100
When acdtime = 0 Then '0'
Else '0'
End as MyPercent,
RepLName+', '+RepFName AS Agent,
SupLName+', '+SupFName AS Sup,
MgrLName+', '+MgrFName AS Manager

From CMS_ECH.dbo.dagent dagent
INNER JOIN InfoQuest.dbo.IQ_Employee_Profiles_v3_AvayaId q on dagent.logid = q.AvayaID

Where row_date between getdate()-90 and getdate()-1
    And row_date between RepToSup_StartDate and RepToSup_EndDate
    And row_date between SupToMgr_StartDate and SupToMgr_EndDate
    And row_date between Avaya_StartDate and Avaya_EndDate
    And row_date between RepQueue_StartDate and RepQueue_EndDate
    And Queue IN ('Pre-Call','Quota','Field Traffic Control','Same Day')
Group By
Queue,
MgrLName+', '+MgrFName,
SupLName+', '+SupFName,
RepLName+', '+RepFName,
logid,
row_date,
acdtime

I have also tried acdtime > 0 and I still get the same error — CodingIsAwesome, Jul 13 '10 at 20:29
Are you sure that the error is coming from there? What is the entire query? Do you know the value of acdtime for the row where the error occurs? What is the type of acdtime? — Mark Byers, Jul 13 '10 at 20:31
I have added When acdtime = Null Then '0' to the case statement and it still produces a divide by zero error. :( — CodingIsAwesome, Jul 13 '10 at 20:36
@Greg Domjan: `SELECT 2/NULL` returns NULL, but not a divide by zero error: http://odata.stackexchange.com/stackoverflow/q/7278/divid-by-zero-test Azure only returns a divide by zero error if the denominator is zero. — OMG Ponies, Jul 13 '10 at 20:37
The type of acdtime is an int. How do I find the value of the acdtime for the row where the error occurs? I assume it must be zero as there are zero's in the data. — CodingIsAwesome, Jul 13 '10 at 20:38
don't use equals as it will always return false with null, use 'is not null' instead, like: acdtime is not null — Larry Smithmier, Jul 13 '10 at 20:40

score 6 · Accepted Answer · answered Jul 13 '10 at 20:42

6

Move the SUM operation and you'll be fine

DECLARE @acdtime decimal
DECLARE @ti_stafftime int

SET @ti_stafftime = 5
SET @acdtime = 0


select 
SUM(

Case 
  When @acdtime != 0 Then CAST(@ti_stafftime as DECIMAL)/@acdtime
  Else '0' 
End )* 100  as MyPercent

This fails

select 


Case 
  When @acdtime != 0 Then SUM( CAST(@ti_stafftime as DECIMAL)/@acdtime) * 100
  Else '0' 
End  as MyPercent

answered Jul 13 '10 at 20:42

Conrad Frix

51,984
12
96
155

Good answer. I had just gotten around to thinking about the order of operations and temp tables. I was just trying to find my Itzik Ben-Gan book when I noticed you had answered it. – Larry Smithmier Jul 13 '10 at 20:52
Excellent, I changed it around to look like this SUM(CASE When acdtime != 0 THEN (CAST(ti_stafftime as DECIMAL)/acdtime)*100 ELSE '0' END) AS 'MyPercent', Thank you so much! – CodingIsAwesome Jul 13 '10 at 20:52

Easy SQL Divide By Zero Occurring when Syntax Seems Correct

1 Answers1