Division Problem in SQL 2005

Question

Post reply

Division Problem in SQL 2005

robertm-772679

Old Hand

Points: 350
More actions
October 16, 2009 at 8:08 am

#142569

Why is it that 1 divided by any number larger then 1 always returns a 0? How can I get around this?
For example, in SQL, 1/100 = 0. When I do this on a calculator I get the correct answer of .01.
I'm sure the answer is simple but I'm just not seeing it.
Thank you for your help.

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

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

robertm-772679 Old Hand Points: 350 More actions · Answer 1

Sorry folks, I jumped the gun. If I covert the integers to a float data type the division occurs the way I expect it to

Select Cast(1 as Float)/Cast(100 as Float)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

No need to cast everything as float. This will work too.

select 1.0*1/100

-- but not

select 1/100*1.00

SQL resolves each step of a calculation and returns the result using the more precise datatype involved in that set. If you do division with two integers, it returns an integer result. In the examples above:

1.0 * 1 = 1.0

1.0/ 100 = .100000

but

1/100 = 0

0 * 1.0 = 0.0

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

robertm-772679 Old Hand Points: 350 More actions · Answer 3

Thanks for the reply,

The numbers were just used as an example. I'm really using integer fields as part of the equation and your right, I did not have to cast everything as float, just the divisor. This is the working solution I came up with

[font="Courier New"]Update RTRoll Set PercServ = RTRoll.PercServ - ((1/Cast(ServInterval as Float)) * 100),

PercGrind = RTRoll.PercGrind - ((1/Cast(GrindInterval as Float)) * 100)

From RTPosition

Left Join RTRoll on RTPosition.CurrRollId = RTRoll.RollId

Left Join RTMach on RTPosition.RTMachId = RTMach.RTMachId

Where RTPosition.CurrRollId Is Not Null and RTMach.StopCalc = 0[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

robertm-772679 (10/18/2009)
Thanks for the reply,
The numbers were just used as an example. I'm really using integer fields as part of the equation and your right, I did not have to cast everything as float, just the divisor. This is the working solution I came up with
[font="Courier New"]Update RTRoll Set PercServ = RTRoll.PercServ - ((1/Cast(ServInterval as Float)) * 100),
PercGrind = RTRoll.PercGrind - ((1/Cast(GrindInterval as Float)) * 100)
From RTPosition
Left Join RTRoll on RTPosition.CurrRollId = RTRoll.RollId
Left Join RTMach on RTPosition.RTMachId = RTMach.RTMachId
Where RTPosition.CurrRollId Is Not Null and RTMach.StopCalc = 0[/font]

You don't really need to CAST it to float... just multiply it time 1.0 as suggested for the integer solution posted above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Also, I know it's unnatural, but for the best accuracy, multiply by 100 first, then divide. If you do that, them use 100.0 instead of just 100 and you'll have the best of both worlds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robertm-772679 Old Hand Points: 350 More actions · Answer 6

Thank you Bob and Jeff for your excellent feedback. I have modified my solution based on your input. This is a bit simpler and easier to read. No CAST at all needed.

Update RTRoll Set PercServ = RTRoll.PercServ - ((1.0*1/ServInterval) * 100),

PercGrind = RTRoll.PercGrind - ((1.0*1/GrindInterval) * 100)

From RTPosition

Left Join RTRoll on RTPosition.CurrRollId = RTRoll.RollId

Left Join RTMach on RTPosition.RTMachId = RTMach.RTMachId

Where RTPosition.CurrRollId Is Not Null and RTMach.StopCalc = 0

Jeff, I multiplied by 100 first like you suggested but came up with the exact same result. I only need accuracy down to 2 decimal places as I'm trying to calculate a percent.

Anyway, the code works great and thank you guys for all your help! I read SQL Server Central alot but this has been my first post. I'm very happy with the response. You guys are great!

MtnJim Valued Member Points: 74 More actions · Answer 7

Thank you so much. I was tearing my hair out trying to figure out why my division wouldn't work.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

robertm-772679 (10/19/2009)
Thank you Bob and Jeff for your excellent feedback. I have modified my solution based on your input. This is a bit simpler and easier to read. No CAST at all needed.
Update RTRoll Set PercServ = RTRoll.PercServ - ((1.0*1/ServInterval) * 100),
PercGrind = RTRoll.PercGrind - ((1.0*1/GrindInterval) * 100)
From RTPosition
Left Join RTRoll on RTPosition.CurrRollId = RTRoll.RollId
Left Join RTMach on RTPosition.RTMachId = RTMach.RTMachId
Where RTPosition.CurrRollId Is Not Null and RTMach.StopCalc = 0
Jeff, I multiplied by 100 first like you suggested but came up with the exact same result. I only need accuracy down to 2 decimal places as I'm trying to calculate a percent.
Anyway, the code works great and thank you guys for all your help! I read SQL Server Central alot but this has been my first post. I'm very happy with the response. You guys are great!

Then add "0.0" instead of multiplying by "1.0". At the machine language level, it'll shave some time off for the CPU because addition is simpler than multipliction. And, I apologize for the late response.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

MtnJim (5/26/2011)
Thank you so much. I was tearing my hair out trying to figure out why my division wouldn't work.

Ah... you bet and, again, apologies for the late response. I appreciate your feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)