The Sum of a Rounded Column;It's driving me crazy!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The Sum of a Rounded Column;It's driving me crazy!

Author

Topic

AlfieNoakes
Starting Member

14 Posts

Posted - 2003-08-12 : 12:02:59

Hi. Please help me i'm going cRaZy!!

I have a query which I spit out into a CSV file.

The last column is a total which is the sum of a column which is rounded to 2dp. Here's the syntax for it:

ROUND(SUM(CONVERT(Float, COST)), 2) AS [Total Value]

From reading the code, the value is rounded to 2dp first, and then SUMmed.

If I look at the CSV file in Excel, and total up the column, I get 1696.47. I can take that as gospel that it is correct.

If I take away the "group by" part of my main query (to get it to total everything), I get: 1696.49, a difference of 2 pence.

Why is this? Does SQL Server not really total up rounded figures?

It seems if I use this syntax:
ROUND(SUM(CONVERT(Float, COST)), 2) AS [Total Value]
I get 1696.49

If I switch the rounding and SUMming, I get an even more inaccurate value:

SUM(ROUND(CONVERT(float, COST), 2)) AS [Total Value]
I get 1696.07

The value must equal 1696.47! Is there any way I can do this? I wondered if I could do a sum around a subquery of all the rounded totals, but I don't think SQL Will allow this. The only other way is to write the CSV query to a temporary table, and then do a SUM on that table, but I really don't want to do that. Please help!

AlfieNoakes
Starting Member

14 Posts

Posted - 2003-08-12 : 12:15:42

I got it!

--------------------------------------------------------------------------------------------------------------------
SELECT SUM(T) AS Total
FROM (SELECT ROUND(SUM(CONVERT(Float, COST)), 2) AS [T]
FROM BaseData INNER JOIN Clients ON BaseData.Account = Clients.Account_Number INNER JOIN
Services ON Basedata.Service = Services.ServiceID
WHERE (CHEETAHFILEDATE = '11/08/2003') AND (BASEDATA.SERVICE IN ('2', '3', '4', '5'))
GROUP BY ACCOUNT, Services.ServiceType, BaseData.CallDate, BaseData.TOD, Clients.Main_Company_Name) DERIVEDTBL
--------------------------------------------------------------------------------------------------------------------

Not quite sure where that 'DERIVEDTBL' bit came from, but it works and that's the main thing!

I guess what it means is that the bit after the from (in between the brackets after) is treated like a new table but is not actually created.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-12 : 13:22:06

Oh, no... it may 'work' today and not work tomorrow. Why you convert
COST namely to float datatype? And why should it be converted at all?
And what is the logic in here: first to sum, then to round the result, or
otherwise - first to round each COST value, then to sum them up?

Maybe you should use instead decimal datatype (coz float datatype is
intrinsically inaccurate):

FROM (SELECT SUM(CONVERT(decimal(12, 2), COST))

Note: you need no to apply the rounding operation in this case. COST is
rounded automatically when converted into decimal value with an 'ordered'
precision (2 digits after the dec. point in the case above).

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-12 : 13:48:37

On the subject of datatypes ... if you're dealing with money then you should use the money data type.

Jonathan
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-12 : 18:50:07

Yes, avoid floats and reals as much as possible. They are approximate values and almost always lose something when being summed or rounded.

AlfieNoakes
Starting Member

14 Posts

Posted - 2003-08-13 : 03:57:02

Sorry, but using:

ROUND(SUM(CONVERT(money, BaseData.COST)), 2)

Gives exactly the same value as converting to a float.

Actually, you're right about the conversion. I don't actually need it. The field COST is already a float data type, therefore the statement can quite happily be this:

SUM(ROUND(BaseData.COST, 2))

Stoad, please explain your statement "it may 'work' today and not work tomorrow", with reference to my "DERIVEDTBL" query...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-13 : 06:36:03

declare @f float(53), @d decimal(12,12)

set @f = 0.899999999999
set @d = 0.899999999999

select round(@f, 2)
select round(@d, 2)

Result:

-----------------------------------------------------
0.90000000000000002 << is not this Deuce unexpected here?

--------------
.900000000000

AlfieNoakes
Starting Member

14 Posts

Posted - 2003-08-13 : 06:39:49

yeah your absolutely right. Floats are bad. I won't use them in future. Thanks. I'm still convinced that the "DERIVEDTBL" query is correct though.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-14 : 02:48:13

Hi AlfieNoakes,

That's the way real (and float) work. They are more or less accurate approximations. This is just what they are, and cannot be changed. I guess they are relicts from the times when storage space was a huge problem.

(BTW, haven't some programmers got rich funneling those trailing bits of floating point numbers into their checking accounts?)

As has been suggested, you use NUMERIC/DECIMAL to get precise numbers.

If possible, change also the underlying data type in the db to these type, so you don't run into these problems. They take up a little bit more disk space, but that shouldn't be a serious issue today.

From my knowledge approximate data types are used when very large numbers are to be calculated, for example in astronomy and not in commerce or industry

Cheers,
Frank

BTW, your problem will get bigger if you do complex calculations or multi-step calculations.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-14 : 03:43:23

Nice wrap-up, Frank.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-14 : 04:57:26