Sql server divide by zero issue

Question

please someone help me to solve this issue: I have this select:

SELECT Cast(( Isnull(price, 0) + Isnull(notarycosts, 0)
              + Isnull(landtax, 0) + Isnull(othertaxes, 0)
              + Isnull(agentfee, 0) + Isnull(cadastralfee, 0)
              + Isnull(tabulationfee, 0)
              + Isnull(certsarcini, 0) ) / ( totalareasqm / 10000 * fixhist ) AS
                   DECIMAL(12, 4)) AS EurPerHa

Sometime i'm getting a divide by zero error and my app it's blocked until i delete the last row from the database. Could I solve somehow this issue?

Thanks!

possible duplicate of [How to avoid the "divide by zero" error in SQL?](http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — LittleBobbyTables - Au Revoir, Dec 06 '12 at 20:38
divide-by-zero is coming from either totalareasqm OR fixhist = 0 so add a WHERE that checks for that in your query — n8wrl, Dec 06 '12 at 20:38
I would try to do a simple google search of the error before posting it as a question here. — Neil, Dec 06 '12 at 21:00
A [quick search](http://stackoverflow.com/search?q=[sql-server]+divide+by+zero+&submit=search) divide by zero) returned a bunch of threads which probably would have answered the question in a lot less time that it took to post here ;-) — Leigh, Dec 06 '12 at 23:42

score 5 · Accepted Answer · answered Dec 06 '12 at 20:40

Either TotalAreaSqm or FixHist is 0. As we cannot divide by zero, what should your script do when a zero exists?

I usually see approaches of either make it NULL or use a known value to make it work. I have no idea what a safe value would be.

Make it null approach

select cast((isnull(price,0)+isnull(notarycosts,0)+isnull(landtax,0)+isnull(othertaxes,0)+isnull(agentfee,0)+isnull(cadastralfee,0)+isnull(tabulationfee,0)+isnull(certsarcini,0))/(NULLIF(TotalAreaSqm, 0)/10000*NULLIF(FixHist, 0) as decimal(12,4)) as EurPerHa

Justin · Answer 2 · 2012-12-06T21:03:36.947

0

Checks FixHist and TotalAreaSqm that are not nulls and not 0.

Code:

select CASE WHEN ISNULL(NULLIF(FixHist,0),0) !=0 
                 AND ISNULL(NULLIF(TotalAreaSqm,0),0) !=0
THEN
cast((isnull(price,0)+
isnull(notarycosts,0)+
isnull(landtax,0)+
isnull(othertaxes,0)+
isnull(agentfee,0)+
isnull(cadastralfee,0)+
isnull(tabulationfee,0)+
isnull(certsarcini,0))/(TotalAreaSqm/10000*FixHist) as decimal(12,4))
ELSE 0 END as EurPerHa

edited Dec 06 '12 at 21:03

answered Dec 06 '12 at 20:36

Justin

9,634
6
35
47

1

incomplete, TotalAreaSqm can be zero as well – RichardTheKiwi Dec 06 '12 at 20:44

score 0 · Answer 3 · edited Dec 06 '12 at 23:46

SELECT IIF( TotalAreaSqm*FixHist=0
            ,null
            , cast(
                    ( isnull(price,0)
                      + isnull(notarycosts,0)
                      + isnull(landtax,0)
                      + isnull(othertaxes,0)
                      + isnull(agentfee,0)
                      + isnull(cadastralfee,0)
                      + isnull(tabulationfee,0)
                      + isnull(certsarcini,0) 
                    )
                    /
                   (TotalAreaSqm / 10000 * FixHist ) 
               as decimal(12,4))
           ) as EurPerHa

score -3 · Answer 4 · answered Dec 06 '12 at 20:38

-3

( isnull(totalareasqm,1) / 10000 * isnull(fixhist,1) )

answered Dec 06 '12 at 20:38

luchosrock

712
10
24

2

That handles NULL values, not when the divisor is zero... – LittleBobbyTables - Au Revoir Dec 06 '12 at 20:40
yeah, totally forgot it, thanks – luchosrock Dec 06 '12 at 20:41

Sql server divide by zero issue

4 Answers4

Make it null approach