Re: Adding the pennies correctly
Date: Tue, 29 Jun 1999 19:21:28 GMT
Message-ID: <377a1738.7138254_at_newshost.us.oracle.com>
A copy of this was sent to "Ian Brown" <browni_at_globalnet.co.uk> (if that email address didn't require changing) On 27 Jun 1999 18:31:59 GMT, you wrote:
>I have run two queries on a 5.8m record table. One verifies that the sum of
>the amount field [NUMERIC (14,2)] is £0.00. When I add a "District" field
>to the output and add GROUP BY the sum of the individual District totals is
>£0.03
>
>Can anyone tell me what is going on and, more importantly, how to properly
>report the District totals for a table that is in balance?
>
>Thanks in advance.
>
>Ian Brown
>
So, you are saying that:
select sum(amount) from T
returns 0.00
But if you query:
select district, sum(amount)
from T
group by district
/
and add up all of the sum(amount)'s -- you get 0.03...
Question: What tool is adding up the sum(amount)'s and what are they fetching into? If its a program that fetches into a FLOAT/DOUBLE to compute the subtotals -- it most certainly might.
Consider this small example. It took me a while to get it "just so" so it might not repeat on your platform. I loaded up a table:
drop table t;
create table t ( x number(14,2) );
begin
for i in 1 .. 100 loop
insert into t values ( 9999+ i/100 );
insert into t values ( -9999 -i/100 );
end loop;
end;
/
Then, I wrote a small pro*c snippet as thus:
static void process()
{
float x;
float total = 0;
varchar total_string[30];
exec sql whenever sqlerror do sqlerror_hard(); exec sql declare c1 cursor for select x from t order by x;
exec sql open c1;
for( ;; )
{
exec sql whenever notfound do break;
exec sql fetch c1 into :x;
total += x;
printf( "x = %g, running Total = %14.2f\n", x, total );
}
exec sql whenever notfound continue;
printf( "Total = %14.2f\n", total );
exec sql select sum(x) into :total_string from t;
printf( "Total String = %.*s\n", total_string.len, total_string.arr ); }
The cursor for loop simulates your "group by district" in a report tool. pretend each row in T has a unique district in it so your sums of the districts are my individual rows.
I fetch the data out sorted by "x" in my example (interestingly -- if you let the data come out in 'natural' order -- the pro*c program gets "0" due to the float rounding that takes place -- it works by accident).
I sum this data manually in a count. obviously -- it should be ZERO but its not, in my example it is -0.03. When I ask the database for the SUM(amount) and put it in a string (no number conversions, no loss of precision) -- i get the correct answer "0".
My output was:
x = -10000, running Total = -10000.00 x = -9999.99, running Total = -19999.99 x = -9999.98, running Total = -29999.97 x = -9999.97, running Total = -39999.94 x = -9999.96, running Total = -49999.90 x = -9999.95, running Total = -59999.85 ... x = 9999.97, running Total = -30000.00 x = 9999.98, running Total = -20000.02 x = 9999.99, running Total = -10000.03 x = 10000, running Total = -0.03 Total = -0.03
Total String = 0
basically replicating your issue exactly (except mines not in pounds but that probably isn't relevant :)
If I turn the float into a double, I find the answer to be -0.00 which is raises a problem as well. It is not zero, its negative. dumping total with a %lg shows that the double value is really:
Total = -3.81988e-11
so its wrong as well. Don't do math in the client unless you have specialized libraries to do so -- floats and doubles will kill you every time. Do the math in sql and or plsql which has 38 digits of precision and will give you the correct answer. Don't fetch the answers into C ints/floats/doubles unless you are willing to live with their limitations.
-- See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Tue Jun 29 1999 - 21:21:28 CEST