Result when subtrahend calls round function











up vote
1
down vote

favorite












I'm dividing two floats, multiplying it by 100 and then subtracting it by 100. I'm returning a percentage.



My question is: why is the final result a float that isn't rounded when the right part of the subtraction returns a float of 2 digits?



These is one sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float)) -- correct

/* 2 */
-- Returns 95.6521739130435, which is correct.
select 100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))) --correct

/* 3 */
-- It's the same as previous one, but with a ROUND
-- Returns 95.65, which is correct.
select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)
|-------------- This returns 95.65 --------------------------------|


Another sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float))

/* 2 */
-- Returns 0.9565, which is correct.
select round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 3 */
-- Returns 95.65, which is correct.
select 100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-(100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4))
|-------------------- This returns 95.65 --------------------------------|


I'm just curious as to why this happens, although it can easily be fixed with one ROUND at the beginning:



select round(100-(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float)))), 2)


The reason I ask is because it's not something that can be easily reproduced. I tried reproducing it, and out of 2,000 times, it only occurred 12 times. That's less than 1%, but with floats with repetitive numbers after the 2nd decimal (ie. 3.47999999999), which makes sense:



declare @rand int = 1
While(@rand <= 2000)
begin
select 100-round(100*(cast(abs(checksum(NewId()) % 1500) as float)/(cast(abs(checksum(NewId()) % 1500) as float) + cast(abs(checksum(NewId()) % 1500) as float))),2)
set @rand = @rand + 1
end


I guess my other question is: what type is the sql editor returning when it returns 95.65 with select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)?










share|improve this question




















  • 3




    In a nutshell, this happens because SELECT 100e - 95.65e gives 4.34999999999999, due to the inability of FLOAT to represent 95.65 exactly. This is why the result needs to be rounded again if you want a "proper" result. DECIMAL has no such problem, which is why SELECT 100 - 95.65 gives an exact result. The same does not happen with the 5th result simply because the string representation happens to round correctly. SELECT FORMAT(100-(round(100*(cast(424 as float)/(cast(424 as float) + cast(1312 as float))),2)), 'G17') will reveal the "actual" result: 75.579999999999998.
    – Jeroen Mostert
    Nov 9 at 22:05












  • FORMAT is not available on SQL Server 2008 -- STR(x, 30, 17) will achieve roughly the same, though.
    – Jeroen Mostert
    Nov 9 at 22:10










  • Type 100-95.65 in your browser's JavaScript console and see what happens.
    – Salman A
    Nov 9 at 22:18










  • I get 4.349999999999994
    – fdkgfosfskjdlsjdlkfsf
    Nov 9 at 22:19






  • 2




    Round of a float returns a float which is an approximate numeric type. If you want the final result rounded then that is what you ought to round, but you'll still get a float unless you cast or convert to an exact numeric type, e.g. decimal.
    – HABO
    Nov 10 at 3:22















up vote
1
down vote

favorite












I'm dividing two floats, multiplying it by 100 and then subtracting it by 100. I'm returning a percentage.



My question is: why is the final result a float that isn't rounded when the right part of the subtraction returns a float of 2 digits?



These is one sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float)) -- correct

/* 2 */
-- Returns 95.6521739130435, which is correct.
select 100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))) --correct

/* 3 */
-- It's the same as previous one, but with a ROUND
-- Returns 95.65, which is correct.
select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)
|-------------- This returns 95.65 --------------------------------|


Another sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float))

/* 2 */
-- Returns 0.9565, which is correct.
select round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 3 */
-- Returns 95.65, which is correct.
select 100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-(100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4))
|-------------------- This returns 95.65 --------------------------------|


I'm just curious as to why this happens, although it can easily be fixed with one ROUND at the beginning:



select round(100-(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float)))), 2)


The reason I ask is because it's not something that can be easily reproduced. I tried reproducing it, and out of 2,000 times, it only occurred 12 times. That's less than 1%, but with floats with repetitive numbers after the 2nd decimal (ie. 3.47999999999), which makes sense:



declare @rand int = 1
While(@rand <= 2000)
begin
select 100-round(100*(cast(abs(checksum(NewId()) % 1500) as float)/(cast(abs(checksum(NewId()) % 1500) as float) + cast(abs(checksum(NewId()) % 1500) as float))),2)
set @rand = @rand + 1
end


I guess my other question is: what type is the sql editor returning when it returns 95.65 with select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)?










share|improve this question




















  • 3




    In a nutshell, this happens because SELECT 100e - 95.65e gives 4.34999999999999, due to the inability of FLOAT to represent 95.65 exactly. This is why the result needs to be rounded again if you want a "proper" result. DECIMAL has no such problem, which is why SELECT 100 - 95.65 gives an exact result. The same does not happen with the 5th result simply because the string representation happens to round correctly. SELECT FORMAT(100-(round(100*(cast(424 as float)/(cast(424 as float) + cast(1312 as float))),2)), 'G17') will reveal the "actual" result: 75.579999999999998.
    – Jeroen Mostert
    Nov 9 at 22:05












  • FORMAT is not available on SQL Server 2008 -- STR(x, 30, 17) will achieve roughly the same, though.
    – Jeroen Mostert
    Nov 9 at 22:10










  • Type 100-95.65 in your browser's JavaScript console and see what happens.
    – Salman A
    Nov 9 at 22:18










  • I get 4.349999999999994
    – fdkgfosfskjdlsjdlkfsf
    Nov 9 at 22:19






  • 2




    Round of a float returns a float which is an approximate numeric type. If you want the final result rounded then that is what you ought to round, but you'll still get a float unless you cast or convert to an exact numeric type, e.g. decimal.
    – HABO
    Nov 10 at 3:22













up vote
1
down vote

favorite









up vote
1
down vote

favorite











I'm dividing two floats, multiplying it by 100 and then subtracting it by 100. I'm returning a percentage.



My question is: why is the final result a float that isn't rounded when the right part of the subtraction returns a float of 2 digits?



These is one sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float)) -- correct

/* 2 */
-- Returns 95.6521739130435, which is correct.
select 100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))) --correct

/* 3 */
-- It's the same as previous one, but with a ROUND
-- Returns 95.65, which is correct.
select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)
|-------------- This returns 95.65 --------------------------------|


Another sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float))

/* 2 */
-- Returns 0.9565, which is correct.
select round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 3 */
-- Returns 95.65, which is correct.
select 100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-(100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4))
|-------------------- This returns 95.65 --------------------------------|


I'm just curious as to why this happens, although it can easily be fixed with one ROUND at the beginning:



select round(100-(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float)))), 2)


The reason I ask is because it's not something that can be easily reproduced. I tried reproducing it, and out of 2,000 times, it only occurred 12 times. That's less than 1%, but with floats with repetitive numbers after the 2nd decimal (ie. 3.47999999999), which makes sense:



declare @rand int = 1
While(@rand <= 2000)
begin
select 100-round(100*(cast(abs(checksum(NewId()) % 1500) as float)/(cast(abs(checksum(NewId()) % 1500) as float) + cast(abs(checksum(NewId()) % 1500) as float))),2)
set @rand = @rand + 1
end


I guess my other question is: what type is the sql editor returning when it returns 95.65 with select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)?










share|improve this question















I'm dividing two floats, multiplying it by 100 and then subtracting it by 100. I'm returning a percentage.



My question is: why is the final result a float that isn't rounded when the right part of the subtraction returns a float of 2 digits?



These is one sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float)) -- correct

/* 2 */
-- Returns 95.6521739130435, which is correct.
select 100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))) --correct

/* 3 */
-- It's the same as previous one, but with a ROUND
-- Returns 95.65, which is correct.
select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)
|-------------- This returns 95.65 --------------------------------|


Another sequence:



/* 1 */ 
-- Returns 0.956521739130435, which is correct.
select cast(198 as float)/(cast(198 as float) + cast(9 as float))

/* 2 */
-- Returns 0.9565, which is correct.
select round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 3 */
-- Returns 95.65, which is correct.
select 100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4)

/* 4 */
-- Returns 4.34999999999999, should be 100-95.65, but it's not. ROUND is ignored. Why?
select 100-(100*round(cast(198 as float)/(cast(198 as float) + cast(9 as float)), 4))
|-------------------- This returns 95.65 --------------------------------|


I'm just curious as to why this happens, although it can easily be fixed with one ROUND at the beginning:



select round(100-(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float)))), 2)


The reason I ask is because it's not something that can be easily reproduced. I tried reproducing it, and out of 2,000 times, it only occurred 12 times. That's less than 1%, but with floats with repetitive numbers after the 2nd decimal (ie. 3.47999999999), which makes sense:



declare @rand int = 1
While(@rand <= 2000)
begin
select 100-round(100*(cast(abs(checksum(NewId()) % 1500) as float)/(cast(abs(checksum(NewId()) % 1500) as float) + cast(abs(checksum(NewId()) % 1500) as float))),2)
set @rand = @rand + 1
end


I guess my other question is: what type is the sql editor returning when it returns 95.65 with select round(100*(cast(198 as float)/(cast(198 as float) + cast(9 as float))),2)?







sql-server tsql sql-server-2008






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 12 at 14:10

























asked Nov 9 at 21:49









fdkgfosfskjdlsjdlkfsf

1,057936




1,057936








  • 3




    In a nutshell, this happens because SELECT 100e - 95.65e gives 4.34999999999999, due to the inability of FLOAT to represent 95.65 exactly. This is why the result needs to be rounded again if you want a "proper" result. DECIMAL has no such problem, which is why SELECT 100 - 95.65 gives an exact result. The same does not happen with the 5th result simply because the string representation happens to round correctly. SELECT FORMAT(100-(round(100*(cast(424 as float)/(cast(424 as float) + cast(1312 as float))),2)), 'G17') will reveal the "actual" result: 75.579999999999998.
    – Jeroen Mostert
    Nov 9 at 22:05












  • FORMAT is not available on SQL Server 2008 -- STR(x, 30, 17) will achieve roughly the same, though.
    – Jeroen Mostert
    Nov 9 at 22:10










  • Type 100-95.65 in your browser's JavaScript console and see what happens.
    – Salman A
    Nov 9 at 22:18










  • I get 4.349999999999994
    – fdkgfosfskjdlsjdlkfsf
    Nov 9 at 22:19






  • 2




    Round of a float returns a float which is an approximate numeric type. If you want the final result rounded then that is what you ought to round, but you'll still get a float unless you cast or convert to an exact numeric type, e.g. decimal.
    – HABO
    Nov 10 at 3:22














  • 3




    In a nutshell, this happens because SELECT 100e - 95.65e gives 4.34999999999999, due to the inability of FLOAT to represent 95.65 exactly. This is why the result needs to be rounded again if you want a "proper" result. DECIMAL has no such problem, which is why SELECT 100 - 95.65 gives an exact result. The same does not happen with the 5th result simply because the string representation happens to round correctly. SELECT FORMAT(100-(round(100*(cast(424 as float)/(cast(424 as float) + cast(1312 as float))),2)), 'G17') will reveal the "actual" result: 75.579999999999998.
    – Jeroen Mostert
    Nov 9 at 22:05












  • FORMAT is not available on SQL Server 2008 -- STR(x, 30, 17) will achieve roughly the same, though.
    – Jeroen Mostert
    Nov 9 at 22:10










  • Type 100-95.65 in your browser's JavaScript console and see what happens.
    – Salman A
    Nov 9 at 22:18










  • I get 4.349999999999994
    – fdkgfosfskjdlsjdlkfsf
    Nov 9 at 22:19






  • 2




    Round of a float returns a float which is an approximate numeric type. If you want the final result rounded then that is what you ought to round, but you'll still get a float unless you cast or convert to an exact numeric type, e.g. decimal.
    – HABO
    Nov 10 at 3:22








3




3




In a nutshell, this happens because SELECT 100e - 95.65e gives 4.34999999999999, due to the inability of FLOAT to represent 95.65 exactly. This is why the result needs to be rounded again if you want a "proper" result. DECIMAL has no such problem, which is why SELECT 100 - 95.65 gives an exact result. The same does not happen with the 5th result simply because the string representation happens to round correctly. SELECT FORMAT(100-(round(100*(cast(424 as float)/(cast(424 as float) + cast(1312 as float))),2)), 'G17') will reveal the "actual" result: 75.579999999999998.
– Jeroen Mostert
Nov 9 at 22:05






In a nutshell, this happens because SELECT 100e - 95.65e gives 4.34999999999999, due to the inability of FLOAT to represent 95.65 exactly. This is why the result needs to be rounded again if you want a "proper" result. DECIMAL has no such problem, which is why SELECT 100 - 95.65 gives an exact result. The same does not happen with the 5th result simply because the string representation happens to round correctly. SELECT FORMAT(100-(round(100*(cast(424 as float)/(cast(424 as float) + cast(1312 as float))),2)), 'G17') will reveal the "actual" result: 75.579999999999998.
– Jeroen Mostert
Nov 9 at 22:05














FORMAT is not available on SQL Server 2008 -- STR(x, 30, 17) will achieve roughly the same, though.
– Jeroen Mostert
Nov 9 at 22:10




FORMAT is not available on SQL Server 2008 -- STR(x, 30, 17) will achieve roughly the same, though.
– Jeroen Mostert
Nov 9 at 22:10












Type 100-95.65 in your browser's JavaScript console and see what happens.
– Salman A
Nov 9 at 22:18




Type 100-95.65 in your browser's JavaScript console and see what happens.
– Salman A
Nov 9 at 22:18












I get 4.349999999999994
– fdkgfosfskjdlsjdlkfsf
Nov 9 at 22:19




I get 4.349999999999994
– fdkgfosfskjdlsjdlkfsf
Nov 9 at 22:19




2




2




Round of a float returns a float which is an approximate numeric type. If you want the final result rounded then that is what you ought to round, but you'll still get a float unless you cast or convert to an exact numeric type, e.g. decimal.
– HABO
Nov 10 at 3:22




Round of a float returns a float which is an approximate numeric type. If you want the final result rounded then that is what you ought to round, but you'll still get a float unless you cast or convert to an exact numeric type, e.g. decimal.
– HABO
Nov 10 at 3:22












1 Answer
1






active

oldest

votes

















up vote
0
down vote













To expand on Jeroen's comment:



SQL Server's FLOAT type is a double-precision floating-point value. As with (most) floating point formats, the value is stored in binary. Just as the number 1/3 cannot be represented with a finite number of digits after the decimal, the number 95.65 cannot be represented with a finite number of bits. The closest value to 95.65 that can be stored in a FLOAT has the exact value:



95.650000000000005684341886080801486968994140625


If you subtract that number from 100, you get exactly:



4.349999999999994315658113919198513031005859375


When displayed, this is rounded to 15 significant digits, and the value printed is:



4.34999999999999


As discussed, you can solve this problem by using DECIMAL type instead of FLOAT.



There are many resources available on StackOverflow and elsewhere if you'd like to learn more about floating-point math.



-- EDIT --



I'm going to use parenthesis notation for repeating decimals. When I write



0.(3)


that means



0.333333333333333333333333333... and so on forever.



Let's start at the beginning. 168 can be stored in a float. 168+9 is 177. That can be stored in a float. If you divide 168 by 177 the mathematically correct answer is:



0.95(6521739130434782608695)


But this value cannot be stored in a float. The closest value that can be stored in a float is:



0.9565217391304348115710354250040836632251739501953125


Take that number and multiply by 100 , the mathematically correct answer is:



95.65217391304348115710354250040836632251739501953125


Since you multiplied a float by 100, you get a float, and that number cannot be stored in a float, so the closest possible value is:



95.6521739130434838216388016007840633392333984375


You ask that this float be rounded to 2 digits after the decimal. The mathematically correct answer is:



95.65


But since you asked to round a float, the answer is also a float, and that value cannot be stored in a float. The closest possible value is:



95.650000000000005684341886080801486968994140625


You asked to subtract that from 100. The mathematically correct value is:



4.349999999999994315658113919198513031005859375


As it happens, that value can be stored in a float. So that's the value that's being selected.



When converting this number to a string, SQL Server rounds the result to 15 significant digits. So that number, when printed, appears as:



4.34999999999999


When you ran the same calculation on your Java console, the exact same calculations were performed, but when the value was printed, Java rounded to 16 significant digits:



4.349999999999994


-- Another EDIT --



Why can't 96.65 be stored exactly in a float? The float type stores numbers in binary format. If you want to express 96.65 in binary, the mathematically exact value is:



1011111.1010011001100110011001100110011001100110011001(1001)


You can see the pattern. Just as 1/3 is represented as an infinite repeating value in decimal, this value has an infinite repeating value in binary. You can see the pattern (1001) being repeated over and over.



A float can only hold 53 significant bits. And so this is rounded to:



1011111.1010011001100110011001100110011001100110011010


If you convert that number back to decimal, you get the exact value:



95.650000000000005684341886080801486968994140625


-- Yet Another Edit --



You ask what happens when you call Round again on the result.



We started with the number:



4.349999999999994315658113919198513031005859375



You ask that this be rounded to 2 places. The mathematically correct answer is:



4.35



Since you are rounding a float, this result must also be a float. Express this value in binary. The mathematically correct answer is:



100.0101100110011001100110011001100110011001100110011001(1001)



Again, this is a repeating binary value. But float can't store an infinite number of bits. The value is rounded to 53 significant bits. The result is:



100.0101100110011001100110011001100110011001100110011



If you convert this to decimal, the exact value is:



4.3499999999999996447286321199499070644378662109375



That is the value you selected. Now SQL Server needs to print that on the screen. As before, it is rounded to 15 significant digits. The result is:



4.35000000000000



It removes the trailing zeros, and the result you see on the screen is:



4.35



The last round did nothing magic. The answer is still stored as a float, and the answer is still not an exact value. As it happens SQL Server chooses to round values to 15 significant digits when printing a float. In this case, that rounded value happened to match the exact value you were expecting.



If values were rounded to 14 places when printing them, the original query would have appeared to have the value you expected.



If values were rounded to 16 places, then the result of the final round would be shown as



4.3499999999999996






share|improve this answer























  • The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:00












  • The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
    – David Dubois
    Nov 12 at 13:15










  • So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:17












  • The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
    – David Dubois
    Nov 12 at 13:22












  • Thanks for all your help. I edited the question since your reply makes sense.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 14:11











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53233663%2fresult-when-subtrahend-calls-round-function%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
0
down vote













To expand on Jeroen's comment:



SQL Server's FLOAT type is a double-precision floating-point value. As with (most) floating point formats, the value is stored in binary. Just as the number 1/3 cannot be represented with a finite number of digits after the decimal, the number 95.65 cannot be represented with a finite number of bits. The closest value to 95.65 that can be stored in a FLOAT has the exact value:



95.650000000000005684341886080801486968994140625


If you subtract that number from 100, you get exactly:



4.349999999999994315658113919198513031005859375


When displayed, this is rounded to 15 significant digits, and the value printed is:



4.34999999999999


As discussed, you can solve this problem by using DECIMAL type instead of FLOAT.



There are many resources available on StackOverflow and elsewhere if you'd like to learn more about floating-point math.



-- EDIT --



I'm going to use parenthesis notation for repeating decimals. When I write



0.(3)


that means



0.333333333333333333333333333... and so on forever.



Let's start at the beginning. 168 can be stored in a float. 168+9 is 177. That can be stored in a float. If you divide 168 by 177 the mathematically correct answer is:



0.95(6521739130434782608695)


But this value cannot be stored in a float. The closest value that can be stored in a float is:



0.9565217391304348115710354250040836632251739501953125


Take that number and multiply by 100 , the mathematically correct answer is:



95.65217391304348115710354250040836632251739501953125


Since you multiplied a float by 100, you get a float, and that number cannot be stored in a float, so the closest possible value is:



95.6521739130434838216388016007840633392333984375


You ask that this float be rounded to 2 digits after the decimal. The mathematically correct answer is:



95.65


But since you asked to round a float, the answer is also a float, and that value cannot be stored in a float. The closest possible value is:



95.650000000000005684341886080801486968994140625


You asked to subtract that from 100. The mathematically correct value is:



4.349999999999994315658113919198513031005859375


As it happens, that value can be stored in a float. So that's the value that's being selected.



When converting this number to a string, SQL Server rounds the result to 15 significant digits. So that number, when printed, appears as:



4.34999999999999


When you ran the same calculation on your Java console, the exact same calculations were performed, but when the value was printed, Java rounded to 16 significant digits:



4.349999999999994


-- Another EDIT --



Why can't 96.65 be stored exactly in a float? The float type stores numbers in binary format. If you want to express 96.65 in binary, the mathematically exact value is:



1011111.1010011001100110011001100110011001100110011001(1001)


You can see the pattern. Just as 1/3 is represented as an infinite repeating value in decimal, this value has an infinite repeating value in binary. You can see the pattern (1001) being repeated over and over.



A float can only hold 53 significant bits. And so this is rounded to:



1011111.1010011001100110011001100110011001100110011010


If you convert that number back to decimal, you get the exact value:



95.650000000000005684341886080801486968994140625


-- Yet Another Edit --



You ask what happens when you call Round again on the result.



We started with the number:



4.349999999999994315658113919198513031005859375



You ask that this be rounded to 2 places. The mathematically correct answer is:



4.35



Since you are rounding a float, this result must also be a float. Express this value in binary. The mathematically correct answer is:



100.0101100110011001100110011001100110011001100110011001(1001)



Again, this is a repeating binary value. But float can't store an infinite number of bits. The value is rounded to 53 significant bits. The result is:



100.0101100110011001100110011001100110011001100110011



If you convert this to decimal, the exact value is:



4.3499999999999996447286321199499070644378662109375



That is the value you selected. Now SQL Server needs to print that on the screen. As before, it is rounded to 15 significant digits. The result is:



4.35000000000000



It removes the trailing zeros, and the result you see on the screen is:



4.35



The last round did nothing magic. The answer is still stored as a float, and the answer is still not an exact value. As it happens SQL Server chooses to round values to 15 significant digits when printing a float. In this case, that rounded value happened to match the exact value you were expecting.



If values were rounded to 14 places when printing them, the original query would have appeared to have the value you expected.



If values were rounded to 16 places, then the result of the final round would be shown as



4.3499999999999996






share|improve this answer























  • The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:00












  • The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
    – David Dubois
    Nov 12 at 13:15










  • So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:17












  • The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
    – David Dubois
    Nov 12 at 13:22












  • Thanks for all your help. I edited the question since your reply makes sense.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 14:11















up vote
0
down vote













To expand on Jeroen's comment:



SQL Server's FLOAT type is a double-precision floating-point value. As with (most) floating point formats, the value is stored in binary. Just as the number 1/3 cannot be represented with a finite number of digits after the decimal, the number 95.65 cannot be represented with a finite number of bits. The closest value to 95.65 that can be stored in a FLOAT has the exact value:



95.650000000000005684341886080801486968994140625


If you subtract that number from 100, you get exactly:



4.349999999999994315658113919198513031005859375


When displayed, this is rounded to 15 significant digits, and the value printed is:



4.34999999999999


As discussed, you can solve this problem by using DECIMAL type instead of FLOAT.



There are many resources available on StackOverflow and elsewhere if you'd like to learn more about floating-point math.



-- EDIT --



I'm going to use parenthesis notation for repeating decimals. When I write



0.(3)


that means



0.333333333333333333333333333... and so on forever.



Let's start at the beginning. 168 can be stored in a float. 168+9 is 177. That can be stored in a float. If you divide 168 by 177 the mathematically correct answer is:



0.95(6521739130434782608695)


But this value cannot be stored in a float. The closest value that can be stored in a float is:



0.9565217391304348115710354250040836632251739501953125


Take that number and multiply by 100 , the mathematically correct answer is:



95.65217391304348115710354250040836632251739501953125


Since you multiplied a float by 100, you get a float, and that number cannot be stored in a float, so the closest possible value is:



95.6521739130434838216388016007840633392333984375


You ask that this float be rounded to 2 digits after the decimal. The mathematically correct answer is:



95.65


But since you asked to round a float, the answer is also a float, and that value cannot be stored in a float. The closest possible value is:



95.650000000000005684341886080801486968994140625


You asked to subtract that from 100. The mathematically correct value is:



4.349999999999994315658113919198513031005859375


As it happens, that value can be stored in a float. So that's the value that's being selected.



When converting this number to a string, SQL Server rounds the result to 15 significant digits. So that number, when printed, appears as:



4.34999999999999


When you ran the same calculation on your Java console, the exact same calculations were performed, but when the value was printed, Java rounded to 16 significant digits:



4.349999999999994


-- Another EDIT --



Why can't 96.65 be stored exactly in a float? The float type stores numbers in binary format. If you want to express 96.65 in binary, the mathematically exact value is:



1011111.1010011001100110011001100110011001100110011001(1001)


You can see the pattern. Just as 1/3 is represented as an infinite repeating value in decimal, this value has an infinite repeating value in binary. You can see the pattern (1001) being repeated over and over.



A float can only hold 53 significant bits. And so this is rounded to:



1011111.1010011001100110011001100110011001100110011010


If you convert that number back to decimal, you get the exact value:



95.650000000000005684341886080801486968994140625


-- Yet Another Edit --



You ask what happens when you call Round again on the result.



We started with the number:



4.349999999999994315658113919198513031005859375



You ask that this be rounded to 2 places. The mathematically correct answer is:



4.35



Since you are rounding a float, this result must also be a float. Express this value in binary. The mathematically correct answer is:



100.0101100110011001100110011001100110011001100110011001(1001)



Again, this is a repeating binary value. But float can't store an infinite number of bits. The value is rounded to 53 significant bits. The result is:



100.0101100110011001100110011001100110011001100110011



If you convert this to decimal, the exact value is:



4.3499999999999996447286321199499070644378662109375



That is the value you selected. Now SQL Server needs to print that on the screen. As before, it is rounded to 15 significant digits. The result is:



4.35000000000000



It removes the trailing zeros, and the result you see on the screen is:



4.35



The last round did nothing magic. The answer is still stored as a float, and the answer is still not an exact value. As it happens SQL Server chooses to round values to 15 significant digits when printing a float. In this case, that rounded value happened to match the exact value you were expecting.



If values were rounded to 14 places when printing them, the original query would have appeared to have the value you expected.



If values were rounded to 16 places, then the result of the final round would be shown as



4.3499999999999996






share|improve this answer























  • The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:00












  • The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
    – David Dubois
    Nov 12 at 13:15










  • So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:17












  • The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
    – David Dubois
    Nov 12 at 13:22












  • Thanks for all your help. I edited the question since your reply makes sense.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 14:11













up vote
0
down vote










up vote
0
down vote









To expand on Jeroen's comment:



SQL Server's FLOAT type is a double-precision floating-point value. As with (most) floating point formats, the value is stored in binary. Just as the number 1/3 cannot be represented with a finite number of digits after the decimal, the number 95.65 cannot be represented with a finite number of bits. The closest value to 95.65 that can be stored in a FLOAT has the exact value:



95.650000000000005684341886080801486968994140625


If you subtract that number from 100, you get exactly:



4.349999999999994315658113919198513031005859375


When displayed, this is rounded to 15 significant digits, and the value printed is:



4.34999999999999


As discussed, you can solve this problem by using DECIMAL type instead of FLOAT.



There are many resources available on StackOverflow and elsewhere if you'd like to learn more about floating-point math.



-- EDIT --



I'm going to use parenthesis notation for repeating decimals. When I write



0.(3)


that means



0.333333333333333333333333333... and so on forever.



Let's start at the beginning. 168 can be stored in a float. 168+9 is 177. That can be stored in a float. If you divide 168 by 177 the mathematically correct answer is:



0.95(6521739130434782608695)


But this value cannot be stored in a float. The closest value that can be stored in a float is:



0.9565217391304348115710354250040836632251739501953125


Take that number and multiply by 100 , the mathematically correct answer is:



95.65217391304348115710354250040836632251739501953125


Since you multiplied a float by 100, you get a float, and that number cannot be stored in a float, so the closest possible value is:



95.6521739130434838216388016007840633392333984375


You ask that this float be rounded to 2 digits after the decimal. The mathematically correct answer is:



95.65


But since you asked to round a float, the answer is also a float, and that value cannot be stored in a float. The closest possible value is:



95.650000000000005684341886080801486968994140625


You asked to subtract that from 100. The mathematically correct value is:



4.349999999999994315658113919198513031005859375


As it happens, that value can be stored in a float. So that's the value that's being selected.



When converting this number to a string, SQL Server rounds the result to 15 significant digits. So that number, when printed, appears as:



4.34999999999999


When you ran the same calculation on your Java console, the exact same calculations were performed, but when the value was printed, Java rounded to 16 significant digits:



4.349999999999994


-- Another EDIT --



Why can't 96.65 be stored exactly in a float? The float type stores numbers in binary format. If you want to express 96.65 in binary, the mathematically exact value is:



1011111.1010011001100110011001100110011001100110011001(1001)


You can see the pattern. Just as 1/3 is represented as an infinite repeating value in decimal, this value has an infinite repeating value in binary. You can see the pattern (1001) being repeated over and over.



A float can only hold 53 significant bits. And so this is rounded to:



1011111.1010011001100110011001100110011001100110011010


If you convert that number back to decimal, you get the exact value:



95.650000000000005684341886080801486968994140625


-- Yet Another Edit --



You ask what happens when you call Round again on the result.



We started with the number:



4.349999999999994315658113919198513031005859375



You ask that this be rounded to 2 places. The mathematically correct answer is:



4.35



Since you are rounding a float, this result must also be a float. Express this value in binary. The mathematically correct answer is:



100.0101100110011001100110011001100110011001100110011001(1001)



Again, this is a repeating binary value. But float can't store an infinite number of bits. The value is rounded to 53 significant bits. The result is:



100.0101100110011001100110011001100110011001100110011



If you convert this to decimal, the exact value is:



4.3499999999999996447286321199499070644378662109375



That is the value you selected. Now SQL Server needs to print that on the screen. As before, it is rounded to 15 significant digits. The result is:



4.35000000000000



It removes the trailing zeros, and the result you see on the screen is:



4.35



The last round did nothing magic. The answer is still stored as a float, and the answer is still not an exact value. As it happens SQL Server chooses to round values to 15 significant digits when printing a float. In this case, that rounded value happened to match the exact value you were expecting.



If values were rounded to 14 places when printing them, the original query would have appeared to have the value you expected.



If values were rounded to 16 places, then the result of the final round would be shown as



4.3499999999999996






share|improve this answer














To expand on Jeroen's comment:



SQL Server's FLOAT type is a double-precision floating-point value. As with (most) floating point formats, the value is stored in binary. Just as the number 1/3 cannot be represented with a finite number of digits after the decimal, the number 95.65 cannot be represented with a finite number of bits. The closest value to 95.65 that can be stored in a FLOAT has the exact value:



95.650000000000005684341886080801486968994140625


If you subtract that number from 100, you get exactly:



4.349999999999994315658113919198513031005859375


When displayed, this is rounded to 15 significant digits, and the value printed is:



4.34999999999999


As discussed, you can solve this problem by using DECIMAL type instead of FLOAT.



There are many resources available on StackOverflow and elsewhere if you'd like to learn more about floating-point math.



-- EDIT --



I'm going to use parenthesis notation for repeating decimals. When I write



0.(3)


that means



0.333333333333333333333333333... and so on forever.



Let's start at the beginning. 168 can be stored in a float. 168+9 is 177. That can be stored in a float. If you divide 168 by 177 the mathematically correct answer is:



0.95(6521739130434782608695)


But this value cannot be stored in a float. The closest value that can be stored in a float is:



0.9565217391304348115710354250040836632251739501953125


Take that number and multiply by 100 , the mathematically correct answer is:



95.65217391304348115710354250040836632251739501953125


Since you multiplied a float by 100, you get a float, and that number cannot be stored in a float, so the closest possible value is:



95.6521739130434838216388016007840633392333984375


You ask that this float be rounded to 2 digits after the decimal. The mathematically correct answer is:



95.65


But since you asked to round a float, the answer is also a float, and that value cannot be stored in a float. The closest possible value is:



95.650000000000005684341886080801486968994140625


You asked to subtract that from 100. The mathematically correct value is:



4.349999999999994315658113919198513031005859375


As it happens, that value can be stored in a float. So that's the value that's being selected.



When converting this number to a string, SQL Server rounds the result to 15 significant digits. So that number, when printed, appears as:



4.34999999999999


When you ran the same calculation on your Java console, the exact same calculations were performed, but when the value was printed, Java rounded to 16 significant digits:



4.349999999999994


-- Another EDIT --



Why can't 96.65 be stored exactly in a float? The float type stores numbers in binary format. If you want to express 96.65 in binary, the mathematically exact value is:



1011111.1010011001100110011001100110011001100110011001(1001)


You can see the pattern. Just as 1/3 is represented as an infinite repeating value in decimal, this value has an infinite repeating value in binary. You can see the pattern (1001) being repeated over and over.



A float can only hold 53 significant bits. And so this is rounded to:



1011111.1010011001100110011001100110011001100110011010


If you convert that number back to decimal, you get the exact value:



95.650000000000005684341886080801486968994140625


-- Yet Another Edit --



You ask what happens when you call Round again on the result.



We started with the number:



4.349999999999994315658113919198513031005859375



You ask that this be rounded to 2 places. The mathematically correct answer is:



4.35



Since you are rounding a float, this result must also be a float. Express this value in binary. The mathematically correct answer is:



100.0101100110011001100110011001100110011001100110011001(1001)



Again, this is a repeating binary value. But float can't store an infinite number of bits. The value is rounded to 53 significant bits. The result is:



100.0101100110011001100110011001100110011001100110011



If you convert this to decimal, the exact value is:



4.3499999999999996447286321199499070644378662109375



That is the value you selected. Now SQL Server needs to print that on the screen. As before, it is rounded to 15 significant digits. The result is:



4.35000000000000



It removes the trailing zeros, and the result you see on the screen is:



4.35



The last round did nothing magic. The answer is still stored as a float, and the answer is still not an exact value. As it happens SQL Server chooses to round values to 15 significant digits when printing a float. In this case, that rounded value happened to match the exact value you were expecting.



If values were rounded to 14 places when printing them, the original query would have appeared to have the value you expected.



If values were rounded to 16 places, then the result of the final round would be shown as



4.3499999999999996







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 12 at 14:26

























answered Nov 11 at 3:48









David Dubois

2,72611230




2,72611230












  • The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:00












  • The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
    – David Dubois
    Nov 12 at 13:15










  • So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:17












  • The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
    – David Dubois
    Nov 12 at 13:22












  • Thanks for all your help. I edited the question since your reply makes sense.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 14:11


















  • The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:00












  • The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
    – David Dubois
    Nov 12 at 13:15










  • So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 13:17












  • The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
    – David Dubois
    Nov 12 at 13:22












  • Thanks for all your help. I edited the question since your reply makes sense.
    – fdkgfosfskjdlsjdlkfsf
    Nov 12 at 14:11
















The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
– fdkgfosfskjdlsjdlkfsf
Nov 12 at 13:00






The question isn't about float types. It's about why the ROUND in 100-ROUND() is being ignored. In both cases, the right part is returning a float with 2 decimals. I'll be editing the question.
– fdkgfosfskjdlsjdlkfsf
Nov 12 at 13:00














The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
– David Dubois
Nov 12 at 13:15




The round DID work. The value was rounded to 95.65. But since the value is stored in a FLOAT, it could not hold that exact value.
– David Dubois
Nov 12 at 13:15












So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
– fdkgfosfskjdlsjdlkfsf
Nov 12 at 13:17






So you're saying that at the of the day, the ROUND(x, 2) in select 100-ROUND(x, 2) is not being evaluated even though select ROUND(x, 2) returns a rounded float?
– fdkgfosfskjdlsjdlkfsf
Nov 12 at 13:17














The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
– David Dubois
Nov 12 at 13:22






The Round WAS evaluated. The answer mathematically exactly correct result is 95.65. That was corrected calculated. But if you call ROUND on a FLOAT, you get a FLOAT. So that value, 95.65 is stored in a FLOAT. But that exact value can't be stored in a float.
– David Dubois
Nov 12 at 13:22














Thanks for all your help. I edited the question since your reply makes sense.
– fdkgfosfskjdlsjdlkfsf
Nov 12 at 14:11




Thanks for all your help. I edited the question since your reply makes sense.
– fdkgfosfskjdlsjdlkfsf
Nov 12 at 14:11


















 

draft saved


draft discarded



















































 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53233663%2fresult-when-subtrahend-calls-round-function%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)