SQL error

Question

SQL error

newbieuser

SSCrazy Eights

Points: 9423
More actions
September 20, 2017 at 10:22 am

#328673

Hi friends,
I get the below error from this sql
select (serno * 100) / (100 - bargain) from yachtm Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Sample data from yachtm table..
select serno,bargain from yachtm
SERNO BARGAIN
7.90 0.0000
8.67 0.0000
0.00 100.0000
please give your thoughts.. thank you all so much

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

newbieuser - Wednesday, September 20, 2017 10:22 AM
Hi friends,
I get the below error from this sql
select (serno * 100) / (100 - bargain) from yachtm Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Sample data from yachtm table..
select serno,bargain from yachtm
SERNO BARGAIN
7.90 0.0000
8.67 0.0000
0.00 100.0000
please give your thoughts.. thank you all so much

My thoughts are that the error message is 100% accurate. But what is your question?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 2

Phil Parkin - Wednesday, September 20, 2017 11:03 AM
My thoughts are that the error message is 100% accurate. But what is your question?

I agree with Phil. 0 / 0 = [Insert infinite options here] = SQL server error is correct. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

Hi friends,

I understand when bargain=100.0000, I get the divide by zero error. But when bargain =0.0000 and serno=7.90, the result should be 7.9 and I need to use the same logic (serno * 100) / (100 - bargain) for all the records..
Please help..

Thanks a lot again

Thom A SSC Guru Points: 99130 More actions · Answer 4

newbieuser - Wednesday, September 20, 2017 11:22 AM
Hi friends,
I understand when bargain=100.0000, I get the divide by zero error. But when bargain =0.0000 and serno=7.90, the result should be 7.9 and I need to use the same logic (serno * 100) / (100 - bargain) for all the records..
Please help..
Thanks a lot again

What should be the result of the divide by 0?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 5

Is there a way to exclude those records that divide by zero in the SQL and do the calculation only for those records that is not zero in the divide by clause.. Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 6

newbieuser - Wednesday, September 20, 2017 11:31 AM
Is there a way to exclude those records that divide by zero in the SQL and do the calculation only for those records that is not zero in the divide by clause.. Thanks

You would use a where clause to exclude records. In your case add:
WHERE serno > 0
after the from clause.

Sue

Edit: - Oops, didn't pay enough attention to the table and mixed up you columns. See responses below.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Use a case statement to check whether bargain = 100 and only do the division if it's not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246961 More actions · Answer 8

newbieuser - Wednesday, September 20, 2017 11:31 AM
Is there a way to exclude those records that divide by zero in the SQL and do the calculation only for those records that is not zero in the divide by clause.. Thanks

Of course, add
WHERE BARGAIN <> 100
to the bottom of your query.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 9

newbieuser

SSCrazy Eights

Points: 9423

September 20, 2017 at 12:16 pm

#1960242

Thanks a lot everyone

drew.allen SSC Guru Points: 76985 More actions · Answer 10

There is a third option. I don't have the time right now to do a comparison.

select (serno * 100) / NULLIF(100 - bargain, 0) from yachtm

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA