Rounding is Inconsistent

Question

Rounding is Inconsistent

souLTower

SSCarpal Tunnel

Points: 4026
More actions
May 15, 2015 at 12:48 pm

#398468

I thought I was crazy. I have a view that returns data that looks like this (there are 500+ records):
NULL
NULL
NULL
57.875
983.875
115.75
27.35
123
NULL
NULL
NULL
NULL
NULL
And I have a query that looks like this:
select round(SUM(fldAmount3) , 0) AS X FROM myView
WHERE X = 2800
AND Y = 665
GROUP BY Y
If you don't round the total comes to 83918.5 If you round the total comes to 83918 OR 83919. Continually running the query you get the lower number every 10th time or so.
Any ideas why?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Changes in the data between runs?

If you run a query over a static set of data, it will always return the same result (except for nondeterministic functions).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442467

May 15, 2015 at 1:04 pm

#1798314

What are the data types you are using?

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 3

The data does not change between runs. It's static. You can run the non-rounded version a million times and it always returns the same.

Higher up the chain of views there is a calculation which generates the value we're summing on. It looks like this:

Value (float) * Percentage (int) \ 100

Again, the data is static so I would expect that however the calculation is run, the result would always be the same.

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

Float is the problem. Compounded by dividing by an integer.

Is this money? Use decimal(21,5).

Floats are called inexact numerics for this reason. You may never get the same answer twice.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

I don't have an example at hand but I've seen the same sorts of problems with DECIMAL, as well.

As for FLOAT being inaccurate, try dividing 1 by 3 using decimal and then multiplying that answer by 3. You won't get 3. Then, try it with FLOAT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 6

I agree, it's the many calculations that lead up to the sum. As a test I took the output of the view, 500+ records, and stored it in a temp table. You can query for the sum on this able and get consistent results every time. So that leads me to the math that produces the view.

Thanks for the insights everyone

ST