February 14, 2007 at 1:56 pm
I have a sql state as follow:
If you look at the result 5*199.60 or 7*921.06 will not exactly equal to total amount.
Would you tell me what went wrong?
Thank you
Betty
select Product,count(pnref) ProductCnt,
Convert(decimal(15,2), Avg(order_total/100.00)) AveTxnAmt,
Convert(decimal(15,2), sum(order_total/100.00)) CSRTotalByProduct,
avg(calllength) avgcallength
from tis_csrtxn where year(starttxndatetime)=2007 and month(starttxndatetime)=2
group by product
The resutl is:
Product1 5 199.60 998.02 5
Product2 7 921.08 6447.53 9
Product3 1 86.00 86.00 8
Product4 160 1263.22 202114.44 8
February 14, 2007 at 2:19 pm
For product1 you have a total of 998.02
When you divide that by 5 you get 199.604 ~ average.
When it converts 199.604 to dec(15,2) you get 199.60.
When you multiply 199.60 by 5 it != 998.02. When you converted to dec(15,2) you rounded....
If you round your average and then multiply it by count it will be off unless the average is exact.
daralick
February 15, 2007 at 8:04 am
Try making your int numbers float (example: select 5.0 * 199.60, 7.0 * 21.06 )
not just the type, but that the number itself has decimals.
February 15, 2007 at 2:31 pm
If you are concerned about precision and accuracy, then stay away from the float and real datatypes, which only store approximate values.
From BOL: "Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. "
-Eddie
Eddie Wuerch
MCM: SQL
February 15, 2007 at 2:52 pm
Eddie,
That's good comment. then, how they solve the issue? I mean how they accomplish it.
Thanks.
Betty
February 16, 2007 at 1:32 pm
Betty,
The value 6447.53 is effectively a prime number for two decimal places. The true average of the 7 values used to produce that value cannot be expressed with only 2 decimal places.
If this is for a report, then the solution is either:
a) report the average using more decimal places so it can be multiplied back to the sum, if that is important (921.0757 instead of 921.08), or
b) for the people reading the report to understand what is actually being reported, that the averages are approximate.
-Eddie
Eddie Wuerch
MCM: SQL
February 16, 2007 at 6:02 pm
Hi Eddie and Daryl,
Thank you for your help. I appreciated very much.
Sincerely
Betty
February 21, 2007 at 7:51 am
since it is money , why not return value as money, too.
February 21, 2007 at 9:00 am
since it is money , why not return value as money, too.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply