Divide in Select Statement

[berryk]

I am trying to divide two columns in a select statement that pulls information from two different views. I keep getting a 0 in the OOS column. I can Add, Subtract, Multiply, but Divide doesn't seem to work. Here is my select statement that works other than the OOS column.

++++++++++++++++++++++++++
select
a.pvvID, a.PVDescr, a."Exception Counts", b."Total Records",(a."Exception Counts" / b."Total Records" ) as OOS
From A3_Top10 a, A3_TotalRecords b
where a.pvvID=b.pvvID

++++++++++++++++++++++++++++

 

[Catadmin]

Remember that you CANNOT, absolutely cannot, ever divide in SQL by zero. You need to test your TotalRecords field to verify it isn't equal to zero before processing that statement.

Use an If/Then/Else statement here. See Books Online for more details.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

That's some pretty strong language Catadmin! What will happen if I do...will my server's HD fall off?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

While Catadmin makes a good point about divide by zero, I don't think that's your problem. If it was you would get a divide by zero error (no Karl, the HD wouldn't fall off... ;-)).

Instead, I think your problem is that you are dividing two integer values, which will return an integer result. As this can't be decimal, it is being rounded down to 0.

Try adjusting your query like this:

SELECT
  a.pvvID,
  a.PVDescr,
  a.[Exception Counts],
  b.[Total Records],
  [b]1.0 *[/b] a.[Exception Counts] / b.[Total Records] AS OOS
FROM A3_Top10 a JOIN A3_TotalRecords b ON a.pvvID = b.pvvID

Note by multiplying by 1.0 the integers will be converted to decimals and therefore provide a decimal result.

Also not the use of ANSI standard [] as delimiters rather than "", and the ANSI JOIN syntax.

--James

 

[Catadmin]

My apologies. I didn't mean to come across as rude.

Berryk, following up on James' comment, are the two numbers you are using both non-decimal numbers or is one of them decimal/float? If both of them are whole numbers (Int datatype), then there might be another problem.

Again, I am sorry my tone came across bad. I really didn't mean it that way. Divide by zero issues were just the first thing I thought of when I saw the code.

Feel free to slap my wrist..



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

I doubt that any offense was taken. My comment was purely in jest. No need to slap a wrist...if so we'd all be slap-happy.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

You mean we aren't slap happy already??? @=)

BTW, Donutman, I meant to tell you that if someone does divide by Zero in SQL, the entire universe will come to an end. And then we'd be standing around with nothing to do with our time. @=)

Seriously, though, (for those who never thought about the problem), it's always good to test for a Divide by Zero case in your code before doing division. Otherwise, someone else is gonna stick you with bad data and blame you when your code blows up.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gmmastros]

There is a way to fix a hard drive after a divide by zero. You have to crack the case, pull out the hard drive, and shake out the bad bits. Works for me every time.

 

[donutman]

BTW, Donutman, I meant to tell you that if someone does divide by Zero in SQL, the entire universe will come to an end. And then we'd be standing around with nothing to do with our time. @=)

Hold onto your chairs...I'm going to try it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]