I get a division by 0 error in SQL Server query

Hi,
I get a division by 0 error in my sql server query.  How do I fix this error in my query?

Thanks in advance,
mrotor
mainrotorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what is the query?
Aneesh RetnakaranDatabase AdministratorCommented:
you need to write a case statement  for example  a/b  should be written as
 
Case when b <> 0 then a/b else 0 end as result

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
I generally use NULLIF  for my divisor,  that way I don't get a NULL instead of a numeric result that might be misinterpreted.  


select x / nullif(y,0) from your_table


of course, sometimes the desired result is a 0 or -1 or some other dummy value for erroneous conditions.
You could then use case as shown above or coalesce to convert to the fixed value

alternately, you might be able to simply exlude those results with a where clause

select x / y from your_table where y <> 0


last,  maybe the data is bad and zeroes should never happen.  If this is the case,  fix the data and implement a constraint so the zeroes can't happen again.


As you can see, the solutions are trivial,  so this is less a technical issue than a requirements definition.
Ask your users what the desired result is and then go from there.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kanti PrasadCommented:
Hi

SELECT COALESCE(TotalMarks / NULLIF(NumberofSubjects,0), 0) FROM YourTableName
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, you are better off using a Case statement to test for the value of NumberOfSubjects, something like:

CASE WHEN IsNull([NumberOfSubjects], 0) = 0 THEN NULL
          ELSE [TotalMarks] / [NumberOfSubjects]
          END as AvgMarks

Dale
sdstuberCommented:
if you're going to use ISNULL and CASE to force null,  why not just use NULLIF   ?


TotalMarks / NULLIF(NumberOfSubjects,0)

achieves the same result while maintaining clarity and requires fewer operations
Dale FyeOwner, Developing Solutions LLCCommented:
@sdStuber

I guess, since I'm more familiar with Access, I prefer to test for a NULL or 0 as the denominator and set the result to NULL, before attempting the Division.

Yes, X/NULL = NULL, so it would not be averaged, but I guess from my perspective (still getting used to some of SQL Servers quirks), the case statement is more readable.

Dale
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.