'Divide By Zero' error encountered

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  November 12, 2007 at 12:22 pm

  #179267

  Hi in the following query I want to divide two numbers from my table to produce a percent however; I am receiving an error message: 'Divide by zero error encountered.' when I come across a value that is null or zero(?) not sure if just both or onlly null values. Is it possible to modify the select to work around this error when null (or zero) is encountered in the divide by case?

  Thank you in advance.

  left(cast(sum(case when b.contacted = 'on' and a.npa = 'South' then 1 else 0 end) as decimal (6,2)) /

  cast(sum(case when a.npa = 'South' then 1 else 0 end) as decimal (6,2)) * 100, 5) as percent_contacted905

• Johannes Fourie

  Default port

  Points: 1470

  More actions

  November 12, 2007 at 12:29 pm

  #750351

  Hi Marcus

  I suggest you use a Case statement to check if the divide by value is zero, if so supply a default value else do the calculation.

  so do something like this

  case when sum(case when a.npa = 'South' then 1 else 0 end) = '0' then 0

  else left(cast(sum(case when b.contacted = 'on' and a.npa = 'South' then 1 else 0 end) as decimal (6,2)) /

  cast(sum(case when a.npa = 'South' then 1 else 0 end) as decimal (6,2)) * 100, 5) END

  so it will return 0 whenever sum(case when a.npa = 'South' then 1 else 0 end) = zero