rounding in sql server produces strange results

Hi experts

I'm trying to round and calculate some numbers, but sql server yields very strange results.


If you have a look at the queries below and calculate it yourself, you'll get the same results. three times.
depending how you do the rounding.
the unrounded result would be 53.55, I personally would round to 53.6, which is also my desired result.

now.
query 1 produces 53.5
query 2 produces 53.6
query 3 produces 51

apart from the very strange result of query 3, what's really funny are the result of query 1 and 2, because they are almost identical. the only difference is, that query 1 uses user defined variables, while query 2 does not.

If I use a column (float) instead of variables the result is the same

I already tried to cast/convert the values etc.

Hope you have some ideas.

I'm using sql server 2005 express edition
declare @test float
set @test = 25.5
declare @margin float
set @margin = 5 

select round((@test + @test * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5/100))*2, 1)

Open in new window

LVL 3
ArikaelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
select round((@test + @test * (5 *0.01))*2, 1)
test is float and cannot properly be represented in binary form exactly, so you get a value that is JUST UNDER 25.5.  What happens is that after all the calcs, it ends up just a little under 53.55 so it gets rounded down.

select round((25.5 + 25.5 * (5 *0.01))*2, 1)
25.5 here is of type NUMERIC, which retains decimal places and goes through decimal based maths, thereby preserving all the precision you expect

select round((25.5 + 25.5 * (5/100))*2, 1)
This one is simple. Try 5/100 and you will see. int/int = int
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberkiwiCommented:
declare @test float
set @test = 25.5
declare @margin float
set @margin = 5

select round((convert(decimal,@test) + convert(decimal,@test) * (5 *0.01))*2, 1)
select round((convert(float,25.5) + convert(float,25.5) * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5/100.0))*2, 1)

reversal of fortunes
0
halfbloodprinceCommented:
I would do like this and it gives the desired results:

declare @test float
set @test = 25.5
declare @margin float
set @margin = 5

select round((@test + @test * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5/100))*2, 1)
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

halfbloodprinceCommented:
Sorry ignore the previous comment by me.It is supposed to be like this:
declare @test float
set @test = 25.5
declare @margin float
set @margin = 5

declare @Result Float
Set @Result = (Select ROUND((25.5 + 25.5 * (5 *0.01))*2,1))
Select @Result as Result
0
mdagisCommented:
Ok here is the full explanation:

1) The following returns for you 53.6 which is the correct since the result before round is 53.55.
select round((25.5 + 25.5 * (5 *0.01))*2, 1)

2) The one with the variable does not produces the correct result because the variables are not numeric (10,1). Try this and it will work:
declare @test numeric (10,1)
set @test = 25.5
select round((@test + @test * (5 *0.01))*2, 1)

3) The one with the division you have this result because the 5/100 = 0. Try doing select 5/100 = 0 and you will understand what I am saying. In order to work remove the parenthesis from the division like:

select round((25.5 + 25.5 * 5/100)*2, 1)




All of the above now should return 53.6

0
ArikaelAuthor Commented:
Hi experts

thanks for your answer. it works

so, I assume float is not a good choice when working with money?
should I better use decimal or money?
0
mdagisCommented:
I almost always use numeric(14,2)
0
cyberkiwiCommented:
For currency/money, use money.
For calculations on purely decimal values, use decimal.
Float  (double) for things where you really don't care about ending with a fixed precision and am likely to carry through the exact precision to the very end, such as lat/longitude, golden ratio, scientific calculations etc.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.