SQL Gurus: A conundrum...

[olllllo]

at least it was for me.

Rounding to 2 decimal places 2.475 should give you 2.48 and 2.525 should return 2.53.

But what if the values are already floats

Select 2.475,
Round(2.475,2) as [Correct Answer],
Cast(2.475 as Float) as [CastFloat],
Convert(float,2.475) as [convertFloat],
Round(Cast(2.475 as Float),2) as [RoundedCastFloat],
Round(Convert(float,2.475),2) as [RoundedconvertFloat]
 
Select 2.525,
Round(2.525,2) as [Correct Answer],
Cast(2.525 as Float) as [CastFloat],
Convert(float,2.525) as [convertFloat],
Round(Cast(2.525 as Float),2) as [RoundedCastFloat],
Round(Convert(float,2.525),2) as [RoundedconvertFloat]

Same SQL, same sig figs, different answer.

Rounding a float gives you a correct answer for 2.475 (2.48) but not 2.525 (2.52).

I have a solution but it's not very intuitive.

 

[olllllo]

Note:
Due to the nature of my data set, the values are already floats (dynamically obtained) and I'm just demonstraing that Cast and Convert are essentially the same here.

Also, I know I need 2 figs here, but I may not in the future, so casting as a decimal or real don't work either.

 

[brett]

Is it an option to manipulate the values outside of the SQL query? For example, pull down the values, and only round for display purposes?

If the values never need to have more than two digits precision, could you round them before insertion into the database?

Honestly, I'm at a loss why Round(2.525,2) would give 2.52.... what DB software are you using?

 

[maltMonkey]

sounds like a floating point error, although I don't recall ever seeing one like that.

mysql 5.1 gives the correct values....like brett said, what dbms?

 

[olllllo]

MS SQL Server 2005

Round(2.525 ,2) works
Round(Cast(2.525 as Float),2) does not

Everything must be done in SQL because these values are the pre-cursor for a correlation using only T-SQL.

 

[VTHawkeeye]

I believe the rule for rounding is if it is exactly "0.5" then you round to the even digit. So in your rounding to the 2nd place after the decimal, with only 3 digits after the decimal, and the third digit is 5, then the 2nd digit should "round" to the closest even digit. Look at the "Round-to-even method" on the rounding page in wikipedia: http://en.wikipedia.org/wiki/Rounding

But as others have said, it probably depends on what DBMS your using.

 

[olllllo]

So running this in MS SQL

Select
Round(2.525 ,2) works gives you 2.53
Round(Cast(2.525 as Float),2) fails gives you 2.52

Select
Round(2.475,2) works! gives you 2.48
Round(Cast(2.475 as Float),2) works! gives you 2.48

But mysql does not give you that?

Regarding the rules of rounding and even and odd....
I had forgotten about that but that does not explain this behavior ^^^.

 

[bradsul]

You've run into one of the fun problems with floating point numbers.

Give this a quick read to shed some light.

 

[fusion94]

MS SQL Server 2005

sorry to be an ass here but it is MS Math...

Who hasn't seen this?

36 minutes to Install

2 hours later it's down to 14 minutes.

 

[olllllo]

You've run into one of the fun problems with floating point numbers.

Give this a quick read to shed some light.

Thanks Brad.
I scanned it and will re-read it tomorrow at work.

The IEEE 754 reerence led me to the solution earlier in the day and that is to cast it as money.

http://technet.microsoft.com/en-us/library/ms187912.aspx?wt.svl=firstflyout

I thought maybe there was a more elegant way to do it, but perhaps not.

 

[bradsul]

Unfortunately you're pretty limited if the source data is already a float. Once it's an approximate number it's tough to go back.

 

[jyrenth]

The way floating point numbers round in the machine is as follows:

1) If rounding one way gives less error than rounding the other, then round the way that gives less error (ie: 3.52 rounds to 3.5 because an error of .02 is less than an error of .08)
2) If the error is the same (ie: 3.55 rounding to the tenths place, either way the error is .05), the round in such a way that the last digit is even.

 

[JimC]

See the above link about floating points. This is why:

jim@jim-laptop:~/code$ cat float.c
#include <stdio.h>

int main() {
	double a = 2.475;
	double b = 2.525;

	printf ("a = %.20f\nb = %.20f\n", a, b);
}

jim@jim-laptop:~/code$ gcc -o float float.c
jim@jim-laptop:~/code$ ./float 
a = 2.47500000000000008882
b = 2.52499999999999991118
jim@jim-laptop:~/code$

 

[MikeFlynn74]

This is why I am getting my CISSP- You dont actually hve to know any of this nerdy ****!

 

[Brewtopia]

 

[EvilTOJ]

As soon as I opened the thread my eyes immediately glazed over. I am no help to your cause, as I have no programming knowledge, or competence.