We help IT Professionals succeed at work.

SQL DIvide by Zero Error Encountered when dividing two Case Statemetns

dplowman
dplowman asked
on
How can I fix the divide by zero error when dividing by a case statement that many result in 0? This is happening because the query is grouped by hour, so if there are no abandons during one of the hours it is trying to divide the holding time by 0.
SELECT call_date as Date, DATEPART(hh, call_time) as Hour, COUNT(record_id) as Total_Calls,
SUM(case when time_connect = 0 then 1 else 0 end) as Abandons, 
SUM(case when time_connect <> 0 then 1 else 0 end) as Calls_to_Agents,
sum(case when time_connect <> 0 then 0 else time_holding10 end)/SUM(case when time_connect = 0 then 1 else 0 end) as Hold_Abandon_Average
FROM [EBGSSRS_Reporting].[dbo].[Noble_Inbound_Detail]
group by call_date, DATEPART(hh, call_time)

Open in new window

Comment
Watch Question

Software Engineer
Commented:
try
select *, case when Abandons = 0 then 0 else Calls_to_Agents / Abandons end as Hold_Abandon_Average
from
(
	SELECT call_date as Date, DATEPART(hh, call_time) as Hour, COUNT(record_id) as Total_Calls,
	SUM(case when time_connect = 0 then 1 else 0 end) as Abandons, 
	SUM(case when time_connect <> 0 then 1 else 0 end) as Calls_to_Agents
	FROM [EBGSSRS_Reporting].[dbo].[Noble_Inbound_Detail]
	group by call_date, DATEPART(hh, call_time)
) A

Open in new window

Author

Commented:
Perfect thanks you! What would be the easiest way to round the Hold_abandon_average to the nearest whole number?
select *, case when Abandons = 0 then 0 else Hold_Abandon / Abandons end as Hold_Abandon_Average
from
(
	SELECT call_date as Date, DATEPART(hh, call_time) as Hour, COUNT(record_id) as Total_Calls,
	SUM(case when time_connect = 0 then 1 else 0 end) as Abandons, 
	SUM(case when time_connect <> 0 then 1 else 0 end) as Calls_to_Agents,
	SUM(case when time_connect = 0 then time_holding10 else 0 end) as Hold_Abandon
	FROM [EBGSSRS_Reporting].[dbo].[Noble_Inbound_Detail]
	group by call_date, DATEPART(hh, call_time)
) A

Open in new window

Ephraim WangoyaSoftware Engineer

Commented:

You can use the functions ROUND or CEILING or FLOOR
Ephraim WangoyaSoftware Engineer

Commented:
eg

 select *,  CEILING(case when Abandons = 0 then 0 else Hold_Abandon / Abandons end) as Hold_Abandon_Average
 ......