Curious Case of SUM Function

  • Comments posted to this topic are about the item Curious Case of SUM Function

  • This "spackle-size" article actually packs one hell of a wallop if you take the time to read it and understand the examples given.  People forget about things such as precision, data type precedence, and the data types of returns.  Hat's off to the author!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Try 

    CREATE TABLE #CuriousCaseOfSum(val DECIMAL(18,1) NOT NULL)

    INSERT INTO #CuriousCaseOfSum(val)
    VALUES (1),(2),(3)

    DECLARE @var DECIMAL(12,0)=12.6
        SELECT    @var, Sum(val) , @var+SUM(val)
        FROM    #CuriousCaseOfSum

    DROP TABLE #CuriousCaseOfSum


    where the variable essentially becomes the integer. You get the "sum(val) + @var" portion to result in decimal (18,1) . This though after the @var has been evaluated to 13.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • Notice that even when the scale is > 0 for both the changed behavior may still appear.  It depends on the Precision.


    -- 18,2 and 18,3
    DECLARE @x decimal(18,2) = 1.23, @y decimal(18,3) = 5.555;
    SELECT @x, @y, @x+@y; -- 1.23, 5.555, 6.785
    GO

    -- 38,2 and 38,3
    DECLARE @x decimal(38,2) = 1.23, @y decimal(38,3) = 5.555;
    SELECT @x, @y, @x+@y; -- 1.23, 5.555, 6.79
    GO

  • In the first example you were using "12.6" in the second example "12.5". 
    Please use the same value otherwise someone can get confused. 
    Great article BTW

  • francesco.mantovani - Thursday, August 24, 2017 3:02 PM

    In the first example you were using "12.6" in the second example "12.5". 
    Please use the same value otherwise someone can get confused. 
    Great article BTW

    Thanks, Your feedback is well received.

  • Great article!  I learned something from this so thank you for posting.  My takeaway is simply this: Never define a decimal field with 0 as it's scale.  Does anyone see a problem with that thinking?

  • thisisfutile - Thursday, August 31, 2017 12:32 PM

    Great article!  I learned something from this so thank you for posting.  My takeaway is simply this: Never define a decimal field with 0 as it's scale.  Does anyone see a problem with that thinking?

    My takeaway is to see each decimal definition as a "different data type".  Each should be manually cast to a new decimal type which accommodates all converted types.  How many significant digits are needed to the left, and how many are needed to the right?  The sum of the two numbers should not exceed 38 or it won't work.

  • Bill Talada - Thursday, August 31, 2017 12:45 PM

    thisisfutile - Thursday, August 31, 2017 12:32 PM

    Great article!  I learned something from this so thank you for posting.  My takeaway is simply this: Never define a decimal field with 0 as it's scale.  Does anyone see a problem with that thinking?

    My takeaway is to see each decimal definition as a "different data type".  Each should be manually cast to a new decimal type which accommodates all converted types.  How many significant digits are needed to the left, and how many are needed to the right?  The sum of the two numbers should not exceed 38 or it won't work.

    Right side in the datatype definition is precision and left is total length. sum of these 2 numbers can be greater than 38, but length (left side) cant be greater than 38.

  • Yes, my post about "significant digits to the left" should not be confused with the non-intuitive way we have to define Decimal(Precision,Scale).  Had Microsoft chosen to implement it as Decimal(Left, Right) then we probably wouldn't be having this forum thread in the first place.

  • Bill Talada - Thursday, August 31, 2017 12:45 PM

    thisisfutile - Thursday, August 31, 2017 12:32 PM

    Great article!  I learned something from this so thank you for posting.  My takeaway is simply this: Never define a decimal field with 0 as it's scale.  Does anyone see a problem with that thinking?

    My takeaway is to see each decimal definition as a "different data type".  Each should be manually cast to a new decimal type which accommodates all converted types.  How many significant digits are needed to the left, and how many are needed to the right?  The sum of the two numbers should not exceed 38 or it won't work.

    I see what you mean and I agree.  OP's examples would all calculate correctly if the scale had been accommodated properly.  I've never dealt with OP's exact issue but I'm pretty sure I speak for most of us when I say the optimizer's interpretations have surprised me on more than one occasion.  Thanks for sharing your thoughts.

  • Jeff Moden - Thursday, August 24, 2017 8:37 AM

    This "spackle-size" article actually packs one hell of a wallop if you take the time to read it and understand the examples given.  People forget about things such as precision, data type precedence, and the data types of returns.  Hat's off to the author!

    I have to admit, Jeff, I have such a respect for you in this community that I gave this article my attention.  Frankly, when I see someone post an article and they're relatively new to the community, I don't read it.  Instead, I jump to the comments and see what's being said by the forum gurus.  Since you gave "hat's off", I did read it and I learned something.  Thanks for sharing your thoughts.

  • thisisfutile - Thursday, August 31, 2017 2:23 PM

    Jeff Moden - Thursday, August 24, 2017 8:37 AM

    This "spackle-size" article actually packs one hell of a wallop if you take the time to read it and understand the examples given.  People forget about things such as precision, data type precedence, and the data types of returns.  Hat's off to the author!

    I have to admit, Jeff, I have such a respect for you in this community that I gave this article my attention.  Frankly, when I see someone post an article and they're relatively new to the community, I don't read it.  Instead, I jump to the comments and see what's being said by the forum gurus.  Since you gave "hat's off", I did read it and I learned something.  Thanks for sharing your thoughts.

    Thanks guys 🙂 Glad if I could contribute any thing.

  • Very interesting! Thanks for sharing!

  • Great article, very interesting and thought provoking.

    Thanks again for taking the time to post this.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 15 posts - 1 through 15 (of 15 total)

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