Post reply

Division Problem in SQL 2005

  • 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.

  • October 16, 2009 at 8:17 am

    #1066920

    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)

  • October 17, 2009 at 8:58 am

    #1067328

    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

  • October 18, 2009 at 6:50 pm

    #1067469

    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]

  • October 18, 2009 at 7:21 pm

    #1067474

    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)
    Intro to Tally Tables and Functions

  • October 18, 2009 at 7:22 pm

    #1067475

    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)
    Intro to Tally Tables and Functions

  • October 19, 2009 at 8:10 am

    #1067646

    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!

  • May 26, 2011 at 3:02 pm

    #1330662

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

  • June 15, 2011 at 6:00 pm

    #1339390

    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)
    Intro to Tally Tables and Functions

  • June 15, 2011 at 6:04 pm

    #1339391

    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)
    Intro to Tally Tables and Functions

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

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