[Solved] SQL Server Error: Divide by zero error encountered

makumbi
 
on Jul 22, 2022 06:54 AM
406 Views

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

SELECT [no], class,stream, subject, Total,  [5] as five, [6] as six, [7] as seven, [8] as eight, [9] as nine, [10] as ten, [11] as eleven, [12] as twelve, [13] as thirteen, [14] as fourteen, [15] as fifteen, [16] as sixteen, [17] as seventeen, [18] as eighteen, [19] as nineteen, [20] as twenty, [21] as twentyone, [22] as twentytwo, [23] as twentythree, [24] as twentyfour, [25] as twentyfive, [26] as twentysix, [27] as twentyseven, 
    [28] as twentyeight, [30] as thirty, [29] as twentynine, [31] as thirtyone, [33] as thirtythree, [32] as thirtytwo, [34] as thirtyfour, [35] as thirtyfive, [36] as thirtysix,  case when subject='Div.analysis %' then(round(([1] / Total) * 100,1)) ELSE [1]  end as one, case when subject='Div.analysis %' then(round(([2] / Total) * 100,1)) ELSE [2] end as two, case when subject='Div.analysis %' then(round(([3] / Total) * 100,1)) ELSE [3] end as three, 
    case when subject='Div.analysis %' then(round(([4] / Total) * 100,1)) ELSE [4]end  as four,case when subject='Div.analysis %' then(round(([U] / Total) * 100,1)) else  [U] end  as U,case when subject='Div.analysis %' then(round(([X] / Total) * 100,1)) else  [X] end  as X,
    (round(([1] / Total) * 100,1)) as onep, (round(([2] / Total) * 100,1)) as twop, (round(([3] / Total) * 100,1)) as threep, (round(([4] / Total) * 100,1)) as fourp, (round(([5] / Total) * 100,1)) as fivep,
    (round(([6] / Total) * 100,1)) as sixp, (round(([7] / Total) * 100,1)) as sevenp, (round(([8] / Total) * 100,1)) as eightp, (round(([9] / Total) * 100,1)) as ninep,isnull([1],0)+isnull([2],0)+isnull([3],0) AS Ps,((isnull([1],0)+isnull([2],0)+isnull([3],0))/total)*100 as Psp,isnull([4],0)+isnull([5],0)+isnull([6],0)+isnull([7],0)+isnull([8],0)+isnull([9],0) as Cfnine
from analysis3

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 22, 2022 06:58 AM

Hi makumbi,

Use NULLIF function.

SQL

DECLARE @Test INT
SET @Test = 5
SELECT 10 / NULLIF(@Test, 0)