SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rounding question


Rounding question

Author
Message
charlie-514368
charlie-514368
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 766
Comments posted to this topic are about the item Rounding question
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9373 Visits: 25280
Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
mickyT
mickyT
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1832 Visits: 3317
bitbucket-25253 (11/18/2012)
Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ?
Nope ... I suspect the difference in the results is rather with the floating point for x being slightly less than 3.65. Needless to say, I got it wrong ... should have looked for the trick.
I would say without running this, there would be no way to accurately predict the answer
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34071 Visits: 13270
It would be great to know more about this "tie-breaking rule", otherwise this question is just guess work.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
bochambers
bochambers
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 82
I initially thought that this was an issue of the approximate storage of the FLOAT data type such that the 3.65 was actually being stored as 3.649999999... for example but when using an exact numeric the exact same outcome occurs, e.g.


declare @x DECIMAL(3,2), @y DECIMAL(3,2)

set @x = 3.65
set @y = 3.75

select 'x = ' + str(@x,10,1)
select 'y = ' + str(@y,10,1)



Similarly if the datatype of @x is set too DECIMAL(2,1) then @x is stored as 3.6 not 3.7 whilst @y is stored as 3.8 as expected.

I agree that more information should be provided on this rounding behaviour as this is not an expected behaviour and differs from the behaviour of ROUND(@x,1) which correctly rounds up to 3.7 as expected.
mickyT
mickyT
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1832 Visits: 3317
bochambers (11/18/2012)
I initially thought that this was an issue of the approximate storage of the FLOAT data type such that the 3.65 was actually being stored as 3.649999999... for example but when using an exact numeric the exact same outcome occurs, e.g.


declare @x DECIMAL(3,2), @y DECIMAL(3,2)

set @x = 3.65
set @y = 3.75

select 'x = ' + str(@x,10,1)
select 'y = ' + str(@y,10,1)



Similarly if the datatype of @x is set too DECIMAL(2,1) then @x is stored as 3.6 not 3.7 whilst @y is stored as 3.8 as expected.

I agree that more information should be provided on this rounding behaviour as this is not an expected behaviour and differs from the behaviour of ROUND(@x,1) which correctly rounds up to 3.7 as expected.
STR() may be changing the data type to float prior to conversionCrazy
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3578 Visits: 2766
I think this question is more about guessing rather than tricky one...
select STR(4.65,10,1) -- it will gives 4.7
select STR(3.65,10,1) -- it will gives 3.6

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10863 Visits: 7319
This is an interesting question. The rounding issue here was quite unanticipated.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13081 Visits: 12143
I'm not really happy with the question, and the explanation is just wrong.

When I saw the question, I knew it was going to be related to inaccuracy in the floating point represtentation. So when I saw the answers, I immediately knew to choose the one that made the least logical sense - that's always the case with these kind of questions. Luckily, there was only one illogical answer. (If "3.7/3.7" had been offered as well, I would have had no choice but to run the query, as these answers are impossible to predict unless you are willing to manually convert it to a binary floating point representation in 53 (I think) bits.

I like the answer even less. There are no tie-breaking rules used. If there were, then the answer should have included a reference to a page where these rules are actually documented, and this is defintely not the case. The specific answers returned by this single query may seem to suggest that STR() uses "bankers rounding", but that really is a mere coincidence. Many other values will round differently, as several posters in this topic already have explained.

(By the way, I did some research and found that there has been a discussion on perceived bankers rounding by STR on this very forum - check http://www.sqlservercentral.com/Forums/Topic246556-8-1.aspx).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
martin.whitton
martin.whitton
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 1774
Just out of interest, I tested the rounding of every float value in the sequence 0.65, 1.65, 2.65, etc up to 99.65 using the following code:


declare
@a float
,@x tinyint;

declare
@tbl table
(
col1 tinyint
,col2 decimal(4,1)
);

select
@x=0;

while @x<=100
begin
select
@a=@x+0.65;

insert into
@tbl
select
@a
,round(@a,1);

select
@x=@x+1;
end;

select
col1
,col2
from
@tbl;



I found that 51 rounded down and 49 rounded up, which sounds fairly random except that every number from 4.65 to 15.65 rounds up, as does every number from 64.65 to 99.65.

There must be some rule that governs this, but I think the best lesson is to always treat float values with caution.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search