SQL Server Divide by zero error encountered COALESCE()

0
votes

I have the following statement which is still returning divide by zero error even when using the coalesce expression.

COALESCE(CAST(sum(case when W0.i13 = 1 then 1 else 0 end)AS FLOAT) / CAST(sum(case when W0.i13 <> 0 then 1 else 0 end) AS FLOAT),0)

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

I also tried the following and still get the error

COALESCE(CAST(sum(case when W0.i13 = 1 then 1 else 0 end)AS FLOAT),0) / COALESCE(CAST(sum(case when W0.i13 <> 0 then 1 else 0 end) AS FLOAT),0)
4
sqlsql-server-2008
You are dividing by 0 as setting 0 is your default value in COALESCE - Mahesh
@CoderofCode While that's true, it's not the cause of the problem. COALESCE will happily return 0 if its first argument is zero - regardless of what you put as its second argument. - Frank Schmitt

4 Answers

1
votes

When the SUM getting ZERO. It will be devided by ZERO. Try this...

COALESCE(CAST(sum(case when W0.i13 = 1 then 1 else 0 end)AS FLOAT),1) / COALESCE(CAST((case when sum(case when W0.i13 <> 0 then 1 else 0 end) = 0 then 1 else sum(case when W0.i13 <> 0 then 1 else 0 end) end) AS FLOAT),1)
1
votes

As CoderOfCode pointed out in the comment - you are using 0 as the second argument to COALESCE instead of 1. But your real problem is that the SUM in the divisor gives 0, and COALESCE will happily use that (because 0 != NULL).

You should be able to use a combination of COALESCE / NULL in the divisor part to force COALESCE to use its second argument and therefore avoid the division by 0:

  CAST(
    sum(
      case 
        when W0.i13 = 1 then 1 
        else 0 
      end)
  AS FLOAT) / 
  COALESCE(
    CAST(
      sum(
        case 
          when W0.i13 <> 0 then 1 
          else NULL end) 
    AS FLOAT),
    1)

SQL Fiddle

0
votes

Replace 0 with 1 in coalesce function 0/0 will give error:Divide by zero error encountered.

In "case when W0.i13 = 1 then 1 else 0 end)AS FLOAT" statement when its output is 0 then it will not go for second parameter in coalesce so better to replace ) in case function with other. Upto then it will gives error. better to put other number

It will work

Change as

 COALESCE(CAST(sum(case when W0.i13 = 1 then 1 else 2 end)AS FLOAT),1) / COALESCE(CAST(sum(case when     W0.i13 <> 0 then 1 else 2 end) AS FLOAT),1)
0
votes

Please try below

COALESCE(CAST(sum(case when W0.i13 = 1 then 1 else 0 end)AS FLOAT) /  CAST(sum(case when W0.i13 <> 0 then W0.i13 else 1 end) AS FLOAT),0)

possible culprit here is CAST(sum(case when W0.i13 <> 0 then 1 else 0 end) AS FLOAT) because

if W0.i13 <> 0 holds false then it returns 0