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: 783
Comments posted to this topic are about the item Rounding question
bitbucket-25253
bitbucket-25253
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26663 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4484 Visits: 3320
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 Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110556 Visits: 13338
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 Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4484 Visits: 3320
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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8299 Visits: 2777
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27238 Visits: 7545
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27088 Visits: 12722
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
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: 1830 Visits: 1794
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