Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INCORECT SUM

Status
Not open for further replies.

delphIBase

Programmer
Joined
Aug 11, 2005
Messages
2
Location
PH
MY PROBLEM:

I made a stored procedure which extract the following values from a table

UNITPRICE ITEMQTY
====================== ===========

12.52 16
59.25 6
480.70 1
841.56 10
8786.63 2

and compute amount with the following formula

amount=(unitprice/1.1)* quantity

having defined amount a numeric(15,2) the stored procedure I created returned the following values

AMOUNT
======================

182.05
323.19
437.00
7650.56
15975.69

Using the statement "select sum(amount) from myproc" to get the sum I got

SUM
======================

24568.48

question:
Given the amount extracted why did I arrived with 24568.48 as sum instead of 24568.49 and how will i arrive with the accurate results?THANKS!:>
 
Using your figures the amount I get is 24568.504

Here is what I used:

create table test1
(
unitprice numeric(15,2),
itemqty integer
);
commit;

insert into test1 values (12.52,16);
insert into test1 values (59.25,6);
insert into test1 values (480.70,1);
insert into test1 values (841.56,10);
insert into test1 values (8786.63,2);
commit;


select sum((unitprice/1.1)*itemqty) from test1;

Unless you need 15 digits of precision I'd be inclined to
reduce it to numeric(9,2), at least this will avoid the problem where IB turns it into a real when using a
precision > 9

I may be wrong about this its been a few years since I last read the user manual :-)

I tried it with different precisions 9,2 and 7,2 and got the same result so maybe there is something in the sp that
I am missing.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top