Post reply

it does not add up (quite literaly)

  • August 19, 2009 at 9:19 am

    #132793

    One view full of numbers with at the most one decial place. Sum of all the numbers returns a figue with 9 decimal places.

    SQL

    select '1',sum(populationcount*10)/10 from dbo.vw_olap_projection_population

    UNION

    select '2',sum(populationcount) from dbo.vw_olap_projection_population

    UNION

    select '3',sum(populationcount) from population_projection

    WHERE(substring(code,1,2) = '42' OR substring(code,1,1) = 'G')

    UNION

    select '4',sum(populationcount) from population_projection

    Results

    1824536.9

    2824536.900000001

    3824536.900000001

    410857152.9

    The answer to 1,2,3 should be the same (824536.9). I have attached a word doc with more details. So why does it not add up????

    Thanks.

    Mark.

    :hehe:

  • August 19, 2009 at 9:29 am

    #1041649

    its because youve used float as your column definition , which by definition will contain an approximate value.

    from bol

    Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value


    Clear Sky SQL
    My Blog[/url]

  • August 19, 2009 at 9:40 am

    #1041665

    I thank you. I cant imagine why rhey consider an approximate value a good idea.

    Any suggestions for a suitable dtat type to hold the value 10.1 that is not approximate?

    Thanks

    Ells.

    :w00t:

  • August 19, 2009 at 9:44 am

    #1041669

    I'd use the decimal data type. Check it out in BOL.

  • August 19, 2009 at 11:00 am

    #1041725

    Ells (8/19/2009)

    I thank you. I cant imagine why rhey consider an approximate value a good idea.

    Any suggestions for a suitable dtat type to hold the value 10.1 that is not approximate?

    Thanks

    Ells.

    :w00t:

    It starts making a whole lot more sense when you get into divisions and multiplications. Add and substract do well with precise data types.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • August 19, 2009 at 12:45 pm

    #1041793

    Thanks will look at decimal.

    Ells.

    😎

  • August 20, 2009 at 1:41 am

    #1041967

    was playing with decimal last night

    select CAST(0.5 as decimal)*2

    gives a result of 2

    Now this and the arithmetic issue with float are beginning to bug me. I will go through BOL today but is there someething else I am missing. I think this is mostly happening through SSMS so is there some setting in there that could affect this.

    Thanks.

    Mark.

    :w00t:

  • August 20, 2009 at 1:46 am

    #1041969

    Went to Excel and queried the view with that sql in and Excel gets it wrong.

    :w00t:

  • August 20, 2009 at 1:48 am

    #1041970

    Which is correct because you have not specified a scale in the decimal. Check Bol

    select CAST(0.5 as decimal)

    select CAST(0.5 as decimal(10,5))


    Clear Sky SQL
    My Blog[/url]

  • August 20, 2009 at 1:51 am

    #1041975

    I was hoping to edit that post before anyone viewed it. As I cleicked post my brain kicked in and I remembered scale and precision. Then I guessed that the default precision would be 0.

    Thanks.

    Ells

    :hehe:

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

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