I get the error of Divide by zero error encountered when this part of my query is ran. I have tried to add NULLIF to the denominator but when I do the division does not calculate properly. Only the 100% shows correct and the others all show as 0%. I Bolded
the line where the division begins.

"'Compliance %' = CAST(((SUM (CASE WHEN Left(tcd.Variable9,1) in ('1','2','3','4','5','6','9') " +
"AND CallDisposition IN ('28', '29', '30', '34', '55') " +
"And PeripheralCallType IN ('1','2','4','13','15') " +
"THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) IN ('7','8') AND tcd.CallDisposition IN (99) " + "And tcd.PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END))) / (SUM (CASE WHEN Left(tcd.Variable9,1) = '7' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) = '8' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) NOT IN ('1','2','3','4','5', '6','7','8','9') AND CallDisposition IN (99) " +
"And PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) = '1' AND CallDisposition IN ('28', '29', '30', '34', '55') " +
"And PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) = '3' AND CallDisposition IN ('28', '29', '30', '34', '55') " +
"And PeripheralCallType IN ('1','2','4','13','15') " +
"THEN 1 ELSE 0 END) ) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) = '2' AND CallDisposition IN ('28', '29', '30', '34', '55') " +
"And PeripheralCallType IN ('1','2','4','13','15') " +
"THEN 1 WHEN Left(tcd.Variable9,1) IN ('7','8') AND CallDisposition IN ( '29') " +
"THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) = '4' AND CallDisposition IN ('28', '29', '30', '34', '55') " +
"And PeripheralCallType IN ('1','2','4','13','15') " +
"THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) NOT IN ('1','2','3','4','5', '6','7','8','9') " +
"And tcd.PeripheralCallType IN ('1','2','4','13','15') " +
"AND tcd.CallDisposition IN (28,29,34,55) " +
"THEN 1 ELSE 0 END)) AS NUMERIC(9,4)), " +
"'TotalConference' = (SUM (CASE WHEN Left(tcd.Variable9,1) = '7' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) = '8' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +
"(SUM (CASE WHEN Left(tcd.Variable9,1) NOT IN ('1','2','3','4','5', '6','7','8','9') AND CallDisposition IN (99) " +
"And PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END)), " +
"'TotaliCareTransfers' = (SUM (CASE WHEN Left(tcd.Variable9,1) in ('1','2','3','4','5','6','9') " +
"AND CallDisposition IN ('28', '29', '30', '34', '55') " +
"And PeripheralCallType IN ('1','2','4','13','15') " +
"THEN 1 ELSE 0 END)), " +

2: You should check your statement is correct (Logic and data).

cgreene.gsu

I have tried to add NULLIF to the denominator but when I do the division does not calculate properly. Only the 100% shows correct and the others all show as 0%

If the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, I think some error in your SQL statement.

Member

9 Points

43 Posts

## c# Divide by zero encoutered

May 13, 2016 01:19 PM|cgreene.gsu|LINK

I get the error of Divide by zero error encountered when this part of my query is ran. I have tried to add NULLIF to the denominator but when I do the division does not calculate properly. Only the 100% shows correct and the others all show as 0%. I Bolded the line where the division begins.

"'Compliance %' = CAST(((SUM (CASE WHEN Left(tcd.Variable9,1) in ('1','2','3','4','5','6','9') " +

"AND CallDisposition IN ('28', '29', '30', '34', '55') " +

"And PeripheralCallType IN ('1','2','4','13','15') " +

"THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) IN ('7','8') AND tcd.CallDisposition IN (99) " +

"And tcd.PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END))) / (SUM (CASE WHEN Left(tcd.Variable9,1) = '7' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +"(SUM (CASE WHEN Left(tcd.Variable9,1) = '8' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) NOT IN ('1','2','3','4','5', '6','7','8','9') AND CallDisposition IN (99) " +

"And PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) = '1' AND CallDisposition IN ('28', '29', '30', '34', '55') " +

"And PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) = '3' AND CallDisposition IN ('28', '29', '30', '34', '55') " +

"And PeripheralCallType IN ('1','2','4','13','15') " +

"THEN 1 ELSE 0 END) ) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) = '2' AND CallDisposition IN ('28', '29', '30', '34', '55') " +

"And PeripheralCallType IN ('1','2','4','13','15') " +

"THEN 1 WHEN Left(tcd.Variable9,1) IN ('7','8') AND CallDisposition IN ( '29') " +

"THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) = '4' AND CallDisposition IN ('28', '29', '30', '34', '55') " +

"And PeripheralCallType IN ('1','2','4','13','15') " +

"THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) NOT IN ('1','2','3','4','5', '6','7','8','9') " +

"And tcd.PeripheralCallType IN ('1','2','4','13','15') " +

"AND tcd.CallDisposition IN (28,29,34,55) " +

"THEN 1 ELSE 0 END)) AS NUMERIC(9,4)), " +

"'TotalConference' = (SUM (CASE WHEN Left(tcd.Variable9,1) = '7' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) = '8' AND CallDisposition IN (30) THEN 1 ELSE 0 END)) + " +

"(SUM (CASE WHEN Left(tcd.Variable9,1) NOT IN ('1','2','3','4','5', '6','7','8','9') AND CallDisposition IN (99) " +

"And PeripheralCallType IN ('1','2','4','13','15') THEN 1 ELSE 0 END)), " +

"'TotaliCareTransfers' = (SUM (CASE WHEN Left(tcd.Variable9,1) in ('1','2','3','4','5','6','9') " +

"AND CallDisposition IN ('28', '29', '30', '34', '55') " +

"And PeripheralCallType IN ('1','2','4','13','15') " +

"THEN 1 ELSE 0 END)), " +

Star

10954 Points

2225 Posts

Microsoft

## Re: c# Divide by zero encoutered

May 16, 2016 08:14 AM|Yohann Lu|LINK

Hi cgreene.gsu

From your description, I think you can refer the following suggestions.

1: You can try to avoid a "Division by zero" error. You can try the following methods.

2: You should check your statement is correct (Logic and data).

If the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, I think some error in your SQL statement.

You can refer the following link.

http://www.sql-server-helper.com/error-messages/msg-8134.aspx

Best Regards,

Yohann Lu

MSDN Community Support

Please remember to "Mark as Answer" the responses that resolved your issue.