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

Answer 1

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