Curious Case of SUM Function

Question

Curious Case of SUM Function

utsav

SSC Veteran

Points: 265
More actions
August 24, 2017 at 12:09 am

#332306

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

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

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

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.

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

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.

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

Bill Talada SSChampion Points: 11954 More actions · Answer 3

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

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 4

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

utsav SSC Veteran Points: 265 More actions · Answer 5

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 6

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?

Bill Talada SSChampion Points: 11954 More actions · Answer 7

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.

Avi1 SSCrazy Points: 2323 More actions · Answer 8

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.

Bill Talada SSChampion Points: 11954 More actions · Answer 9

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 10

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 11

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.

utsav SSC Veteran Points: 265 More actions · Answer 12

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.

Alessandro Mortola SSC Eights! Points: 905 More actions · Answer 13

Alessandro Mortola

SSC Eights!

Points: 905

December 24, 2019 at 1:21 pm

#3710230

Very interesting! Thanks for sharing!

ScottPletcher SSC Guru Points: 101108 More actions · Answer 14

Great article, very interesting and thought provoking.

Thanks again for taking the time to post this.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".