Post reply

Using Decimal Colums in SUM function

  • July 21, 2015 at 12:46 am

    #302602

    Hi Folks,

    Recently i have come accross a requirement where i need to design a table.

    There are some columns in table like below with DECIMAL Datatype:

    BldgLength

    BldgHeight

    BldgWeight

    Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.

    Now as per MSDN,

    Precision => 1 - 9

    Storage bytes => 5

    so i can create column as:

    BldgLengthDECIMAL(6,2) DEFAULT 0

    OR

    BldgLengthDECIMAL(9,2) DEFAULT 0

    Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.

    So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.

    So my question is what should be value of DataType for above column ?

    As i was not sure, so thought to ask you, hope you guys got an idea.

    Can you guys please throw some light on above issue.

    Any link/article on above topic would be helpful.

    Thanks in advance.

    Devsql

  • July 21, 2015 at 2:49 am

    #1813781

    declare @num1 decimal(4,2) = 12.56

    declare @num2 decimal(4,2) = 96.54

    declare @num3 decimal(4,2)

    declare @num4 decimal(4,2)

    declare @num5 decimal(4,2)

    select @num3= @num1 + @num2

    select @num4=@num1 * @num2

    select @num5=@num2 / @num1

    select @num3 , @num4 , @num5

    GO

    declare @num1 float = 12.56

    declare @num2 float = 96.54

    declare @num3 float

    declare @num4 float

    declare @num5 float

    select @num3= @num1 + @num2

    select @num4=@num1 * @num2

    select @num5=@num2 / @num1

    select @num3 , @num4 , @num5

    Look up significant digits in maths, decimal allows precision and doesnt like rounding errors.

    Jayanth Kurup[/url]

  • July 21, 2015 at 7:40 am

    #1813838

    Thanks Jayanth for this superb example.

  • July 21, 2015 at 11:40 am

    #1813923

    devsql123 (7/21/2015)

    Thanks Jayanth for this superb example.

    If all you go by is the example that shows a float data type not having a problem where a rather narrowly defined decimal value does have a problem, and conclude that float is the better data type, then you'll completely misinterpret the results. Float cannot guarantee accuracy, and cannot represent certain numbers accurately, so before you rely on such a data type, you need to understand the consequences. That is why you were asked to look up the topic "significant digits". Decimal data types should be designed to hold the maximum practical computation accuracy for any mathematical operation that number is likely to be involved in. That way, you don't run into the "float" problem with accuracy. I'm of the opinion that demonstrating float vs decimal based on decimal(4,2) and expecting float to not look good is terribly misleading, as you don't have anywhere near an apples to apples comparison. After all, float has a numeric precision of 53 digits total, so being compared to a data type with just 4 digits is patently ridiculous. Float may be "convenient", but it's the easy way out of doing the more time consuming work of properly considering your actual computational requirements.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • July 21, 2015 at 9:34 pm

    #1813996

    sgmunson (7/21/2015)

    devsql123 (7/21/2015)

    Thanks Jayanth for this superb example.

    If all you go by is the example that shows a float data type not having a problem where a rather narrowly defined decimal value does have a problem, and conclude that float is the better data type, then you'll completely misinterpret the results. Float cannot guarantee accuracy, and cannot represent certain numbers accurately, so before you rely on such a data type, you need to understand the consequences. That is why you were asked to look up the topic "significant digits". Decimal data types should be designed to hold the maximum practical computation accuracy for any mathematical operation that number is likely to be involved in. That way, you don't run into the "float" problem with accuracy. I'm of the opinion that demonstrating float vs decimal based on decimal(4,2) and expecting float to not look good is terribly misleading, as you don't have anywhere near an apples to apples comparison. After all, float has a numeric precision of 53 digits total, so being compared to a data type with just 4 digits is patently ridiculous. Float may be "convenient", but it's the easy way out of doing the more time consuming work of properly considering your actual computational requirements.

    Careful, now. Float does NOT have a numeric PRECISION of "53 digits total". The maximum precision is "only" 15 digits before it starts rounding (not to be mistaken with the "approximate number" that it uses for some values). "53" is the maximum number of bits that FLOAT can be made to use.

    Also and to your point but a little deeper... if you want to be more accurate with 2 digit decimal places, you should always do your calculations using at least 13 decimal places for multiplication and divisions and only round the answer to 2 decimal places or you get what's known as a "stack tolerance buildup".

    --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)

  • July 22, 2015 at 12:20 am

    #1814000

    I think the Op is measuring the dimensions of a building and are probably not interested in anything beyond 2 decimal places. I wonder if people will ever truly appreciate the approximations made by float datatype.

    Jayanth Kurup[/url]

  • July 22, 2015 at 10:18 am

    #1814157

    Jeff Moden (7/21/2015)

    sgmunson (7/21/2015)

    devsql123 (7/21/2015)

    Thanks Jayanth for this superb example.

    If all you go by is the example that shows a float data type not having a problem where a rather narrowly defined decimal value does have a problem, and conclude that float is the better data type, then you'll completely misinterpret the results. Float cannot guarantee accuracy, and cannot represent certain numbers accurately, so before you rely on such a data type, you need to understand the consequences. That is why you were asked to look up the topic "significant digits". Decimal data types should be designed to hold the maximum practical computation accuracy for any mathematical operation that number is likely to be involved in. That way, you don't run into the "float" problem with accuracy. I'm of the opinion that demonstrating float vs decimal based on decimal(4,2) and expecting float to not look good is terribly misleading, as you don't have anywhere near an apples to apples comparison. After all, float has a numeric precision of 53 digits total, so being compared to a data type with just 4 digits is patently ridiculous. Float may be "convenient", but it's the easy way out of doing the more time consuming work of properly considering your actual computational requirements.

    Careful, now. Float does NOT have a numeric PRECISION of "53 digits total". The maximum precision is "only" 15 digits before it starts rounding (not to be mistaken with the "approximate number" that it uses for some values). "53" is the maximum number of bits that FLOAT can be made to use.

    Also and to your point but a little deeper... if you want to be more accurate with 2 digit decimal places, you should always do your calculations using at least 13 decimal places for multiplication and divisions and only round the answer to 2 decimal places or you get what's known as a "stack tolerance buildup".

    Jeff,

    Thanks for the correction. I had seen 53 as the value in the NUMERIC_PRECISION column on a query against INFORMATTION_SCHEMA.COLUMNS for a float column in a table, and made the wrong assumption on that basis. While the difference between 53 digits and 15 digits is rather significant, even 15 digits as opposed to just 4 makes for an absurd comparison.

    I am rather familiar with what I've always referred to as cumulative error - a result of multiple calculations, each individually limited in precision, such that for accuracy purposes, you can no longer consider the last digit of each result to be accurate, and thus you lose 2 digit's worth of accuracy with each subsequent calculation you make. If you're not careful, your entire result could end up meaningless. Is that what you're referring to as "stacked tolerance buildup"?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • July 22, 2015 at 2:11 pm

    #1814221

    sgmunson (7/22/2015)

    I am rather familiar with what I've always referred to as cumulative error - a result of multiple calculations, each individually limited in precision, such that for accuracy purposes, you can no longer consider the last digit of each result to be accurate, and thus you lose 2 digit's worth of accuracy with each subsequent calculation you make. If you're not careful, your entire result could end up meaningless. Is that what you're referring to as "stacked tolerance buildup"?

    Exactly. It's a much more appropriate term for the physical tolerances of "stacks" of hardware across multiple levels of the hardware but the effect is the same in software.

    --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)

  • July 23, 2015 at 8:00 am

    #1814376

    [font="Comic Sans MS"]FLOAT cannot be counted on for exact math. Save yourself grief and avoid FLOAT like the plague. The decimal number 0.1 cannot be exactly encoded as a binary value, you will always collect spurious insignificant decimals waaaaay after the decimal point.

    Try this:[/font]

    [font="Courier New"]DECLARE @f_Sum FLOAT = 0;

    SET @f_Sum = @f_Sum + 0.1

    SET @f_Sum = @f_Sum + 0.1

    SET @f_Sum = @f_Sum + 0.1

    SET @f_Sum = @f_Sum - 0.3

    IF @f_Sum = 0

    SELECT '@f_Sum is zero'

    ELSE

    SELECT '@f_Sum is NOT zero'

    SELECT @f_Sum[/font]

    [font="Comic Sans MS"]Results:[/font]

    [font="Courier New"]

    ------------------

    @f_Sum is NOT zero

    ----------------------

    5.55111512312578E-17

    [/font]

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

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