CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
d_luffy_de
d_luffy_de

Reputation: 967

Using OR in CASE to Resolve "Divide By Zero" Error

I have a dynamic SQL which generated a SQL query for one of my calculation which resulted in a "Divide by Zero" error

Although I do have CASE statements to check for the error its not working for some reason.

Below is the calculation formula:

CASE WHEN SUM(A) = 0
     THEN 0
     WHEN SUM(B) = 0
     THEN 0
     ELSE
     SUM((C/B))/AVG(A) AS X

I am getting the error because column B has zero value in it.

Can you please help...

Upvotes: 0

Views: 260

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44326

You could write it as simple as this and exclude the CASE:

ISNULL(SUM(C/NULLIF(B,0))/NULLIF(AVG(A),0),0) X

Upvotes: 3

d_luffy_de
d_luffy_de

Reputation: 967

Thanks for the comments and answers. Although both of the above answers didn't seem to work for some reason.

I ended-up using the snippet below to resolve the issue:

CASE 
    WHEN AVG(A) = 0 THEN 0
    ELSE ISNULL( SUM ((C/NULLIF(B,0)))/AVG(A),0)
END AS X

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

If you want to leave your existing checks in place, then you need to add extra checks like this. you need to make sure you dont have any 0 value for B. you may need to add where condition as per your requirement.

    CASE WHEN SUM(A) = 0
         THEN 0
         WHEN SUM(B) = 0
         THEN 0
         WHEN EXISTS(SELECT 1 FROM  YOURTABLE WHERE B=0) -- add WHERE here if you need to.
         THEN 0
         WHEN AVG(A)=0
         THEN 0
         ELSE
         SUM((C/B))/AVG(A) 
   END AS X

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

Since you're using each value of B, not the SUM(B) as the divisor, you should be checking for the existence of B where B = 0. Same goes for A, you should check for AVG(A) instead of SUM(A):

CASE 
    WHEN AVG(A) = 0 THEN 0
    WHEN SUM(CASE WHEN B = 0 THEN 1 ELSE 0 END) > 0 THEN 0
    ELSE SUM((C/B))/AVG(A)
END AS X

Upvotes: 2

Related Questions