Trouble adding numbers with 12 decimal places

[wsmeyer]

In my database I have numbers stored as decimal(16,12) like this:

1.000100000000
3.000100010001
10.000500020003

If I just grab them and echo them to the screen they return correctly but if I try to get the sum of them either in MySQL using

sum(nums) AS num

or grabbing them and then with PHP looping through them and using

$tot +=

they do not add correctly and it is DRIVING ME CRAZY :)

If anyone has any ideas I'd really appreciate a point in the right direction.

William.

[Steerpike]

Editted previous message.

[FalseDawn]

Rounding errors, probably.

What results are you getting in each case?

[coopster]

Welcome to WebmasterWorld, wsmeyer.

It works fine for me. Is it possible it is a version or operating system issue? I ran the code in both a MySQL command line client as well as a PHP script and both returned the expected results.

mysql_query("drop temporary table if exists junk"); 
mysql_query("create temporary table junk (num decimal (16,12))"); 
mysql_query("insert into junk values(1.000100000000),(3.000100010001),(10.000500020003)"); 
$row = mysql_fetch_array(mysql_query("select sum(num) as num from junk")); 
print $row['num']; // prints 14.000700030004 

[Steerpike]

That's interesting, 'cause the following code for me

<?
$num[0] = 1.000100000000;
$num[1] = 3.000100010001;
$num[2] = 10.000500020003;
$total = 0;
echo "A is: $num[0] B is: $num[1] C is: $num[2]";
for($x = 0; $x <2; $x++)
{
$total += $num[$x];
}
echo "<br>Total is: $total";
?>

Outputs the following:
A is: 1.0001 B is: 3.00010001 C is: 10.00050002
Total is: 4.00020001

I wonder what it is.

[FalseDawn]

Well, I guess php is storing the values as floats (or whatever it uses) and is losing some precision.

MySQL decimal fields, by contrast, are designed to be exact to the specified number of digits.

I did read somewhere that you might have to put quotes around values if you are inserting into decimal fields, or mysql will round the values...

[coopster]

Steerpike, your total is indeed correct. Look again, you are only summing the first two elements of the array, you need to say "less than or equal to" in your for loop.

It is quite usual that simple decimal fractions cannot be converted into their internal binary counterparts without a little loss of precision. See the note on Floating point precision when working with Floating point numbers [php.net] for more information. If you find that you aren't getting the precision you desire you can always use the BCMath Arbitrary Precision Mathematics Functions [php.net]. In this case, however, the same results were returned without using them:

<?php 
$num[0] = 1.000100000000; 
$num[1] = 3.000100010001; 
$num[2] = 10.000500020003; 
$total = 0; 
$bctotal = 0; 
$bctotal12 = 0; 
$precision = 12; // precision 
echo "A is: $num[0] B is: $num[1] C is: $num[2]"; 
for($x = 0; $x <=2; $x++) 
{ 
$total += $num[$x]; 
$bctotal = bcadd($bctotal, $num[$x]); 
$bctotal12 = bcadd($bctotal12, $num[$x], $precision); 
} 
echo "<br>Total is: $total"; // 14.00070003 
echo "<br>bcTotal is: $bctotal"; // 14 
echo "<br>bcTotal12 is: $bctotal12"; // 14.000700030000 
exit; 

[PCInk]

Interestingly, computers cannot even store 0.1 accurately. This is because in binary, it is a recursive number (like 1/3 in denary - it becomes 0.333333333333333333').

If you need 100% accuracy, there are two workarounds:

1) store the number multiplied by an amount and slot the decimal place whenever you print the number. Note that you also need to adjust whenever you multiply or divide two of these 'multiplied numbers'. One example of this in practice is accounting systems (as they cannot store 0.1 or 0.01 accurately). What they do is store the amount in cents (or pennies) and slot the decimal place in when you print the numer - so 1 dollar would be stored as 100.

2) instead of floating point, you can use fractions, but you need to write your own procedures for this and it requires two variables for the storage of one number. This is more complicated but usually even more accurate.

So in your example:
1.000100000000 should be stored as 1000100000000
3.000100010001 as 3000100010001
10.000500020003 as 10000500020003

Add them up and divide by the number we multiplied them by (1000000000000, I think!) and the result should be more accurate, in theory at least!

[coopster]

Oops, take part of that back ... ;) I just noticed we lost the last value's precision here, but it is not happening during the addition loop, it happen's when the numbers are first placed in the array. They are indeed float numerics but we lose some precision. Dump them to see what I mean:

$num[0] = 1.000100000000; 
$num[1] = 3.000100010001; 
$num[2] = 10.000500020003; 
var_dump [php.net]($num); exit; 
// prints 
array(3) { 
 [0]=> 
 float(1.0001) 
 [1]=> 
 float(3.00010001) 
 [2]=> 
 float(10.00050002) 
}

I'm certain that FalseDawn is correct here in that there will be a difference in how the MySQL operation is summing the value and retaining precision. Also, it will indeed be returned as a string value so PHP won't be doing any type conversion on us. Here is a var_dump of the $row being returned by the MySQL operation shown earlier:

array(2) { 
 [0]=> 
 string(15) "14.000700030004" 
 ["num"]=> 
 string(15) "14.000700030004" 
}

Two values are shown here because I used mysql_fetch_array() which will return a numeric index as well as the associative index. Note the precision when stored as a string value. Then when we print it from PHP as a string it will of course retain the same value without any type juggling and precision loss.

Now, if we were to bring back the individual values in the SELECT query as opposed to summing them in MySQL we will indeed have to use the bcadd() function as before in order to retain precision. Otherwise the results will be exactly as they are in the last post. That covers that issue...

...So, the next question here would be "How do I retain float precision if I am merely assigning these types of values to a PHP variable (as shown in Steerpike's example)?"

[coopster]

... and that question was answered by FalseDawn, cast them as a string value by placing them in quotation marks ;)

[wsmeyer]

Thanks to everyone for their help!

I haven't figured it out yet, and the plot thickens...

Some background may help. This is for an eccommerce site that I run that sells coffee mugs. We have three sizes of mugs 14oz, 20oz, and travel mugs. We sell the mugs both individually and in sets that are created on the fly. I have now created a "quantString" for each product and set like:

aaaa.bbbbccccdddd

where...
aaaa = total mugs
bbbb = total 14oz mugs
cccc = total 20oz mugs
dddd = total travel mugs

All is working correctly up to and including the point where it adds up the quantString's for each order. If I query the database for the quantString for a single order it returns correctly. It is where I try and get stats for a number of orders or a given time frame that problems arise.

Here is the code for three different ways I have tried to query the database:

$monthStrt = date("m")-1;
$monthEnd = date("m")-1;
$year = date("Y");
$Month = date("F", mktime(0, 0, 0, $monthStrt, 1, $year));
$numDays = cal_days_in_month( CAL_GREGORIAN, $monthEnd, $year);
$sd = mktime(0, 0, 0, $monthStrt, 01, $year);
$ed = mktime(0, 0, 0, $monthEnd, $numDays, $year);

$query = mysql_query("SELECT ordQuantString FROM orders WHERE ordDate BETWEEN '" . date("Y-m-d", $sd) . "' AND '" . date("Y-m-d", $ed) . " 23:59:59'");
$tot =0;
while($rs = mysql_fetch_assoc($query)){
$string = $rs['ordQuantString'];
$tot += $string;

preg_match('/(\d+)+(\.)+(\d\d\d\d)+(\d\d\d\d)+(\d\d\d\d)/', $string, $match_array2);
$numTot += (int)$match_array2[1];
$num14 += (int)$match_array2[3];
$num20 += (int)$match_array2[4];
$numTM += (int)$match_array2[5];
}

print "quantString: " . $tot . "<br />";
print "total: " . $numTot . "<br />";
print "14oz: " . $num14 . "<br />";
print "20oz: " . $num20 . "<br />";
print "travel: " . $numTM . "<br />";

print "<br />";
print "-----------------------------------";
print "<br />";

$query = mysql_query("SELECT ordQuantString FROM orders LIMIT 100");
$tot =0;
while($rs = mysql_fetch_assoc($query)){
$string = $rs['ordQuantString'];
$tot += $string;

preg_match('/(\d+)+(\.)+(\d\d\d\d)+(\d\d\d\d)+(\d\d\d\d)/', $string, $match_array2);
$numTot_2 += (int)$match_array2[1];
$num14_2 += (int)$match_array2[3];
$num20_2 += (int)$match_array2[4];
$numTM_2 += (int)$match_array2[5];
}

print "quantString: " . $tot . "<br />";
print "total: " . $numTot_2 . "<br />";
print "14oz: " . $num14_2 . "<br />";
print "20oz: " . $num20_2 . "<br />";
print "travel: " . $numTM_2 . "<br />";

print "<br />";
print "-----------------------------------";
print "<br />";

$query = mysql_query("SELECT ordQuantString FROM orders LIMIT 10");
$tot =0;
while($rs = mysql_fetch_assoc($query)){
$string = $rs['ordQuantString'];
$tot += $string;

preg_match('/(\d+)+(\.)+(\d\d\d\d)+(\d\d\d\d)+(\d\d\d\d)/', $string, $match_array2);
$numTot_3 += (int)$match_array2[1];
$num14_3 += (int)$match_array2[3];
$num20_3 += (int)$match_array2[4];
$numTM_3 += (int)$match_array2[5];
}

print "quantString: " . $tot . "<br />";
print "total: " . $numTot_3 . "<br />";
print "14oz: " . $num14_3 . "<br />";
print "20oz: " . $num20_3 . "<br />";
print "travel: " . $numTM_3 . "<br />";
?>

Combined outputs...

quantString: 600.04920085006
total: 600
14oz: 492
20oz: 85
travel: 59

-----------------------------------
quantString: 385.03010050003
total: 385
14oz: 301
20oz: 50
travel: 34

-----------------------------------
quantString: 29.002000040005
total: 29
14oz: 20
20oz: 4
travel: 5

As you can see the first one doesn't contain enough decimal places.

Second one doesn't have the right digits either, but the reulting total are correct.

third one is correct, but only for 10 entries.

William.