Member Avatar for freiheit

Can anyone tell me how to achieve this (if there is any?)

Cause this is causing kinda big declination from the sum results i was expecting....

Thanks in advance

  • 4 Contributors
  • 13 Replies
  • 118 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Brief diagnosis: SUM() itself does not invent rounding — the precision you see comes from the numeric types and how MySQL evaluates intermediate arithmetic. Fixed‑point DECIMAL arithmetic can round intermediate division results to the DECIMAL scale, and binary FLOAT/DOUBLE introduces small approximation errors that accumulate when you add many rows. That explains why a few rows match hand calculations but many rows diverge.

Practical fixes to try (in order of convenience):

  • Force higher fixed precision in the expression by casting subexpressions to a DECIMAL with enough scale before dividing/multiplying. Example pattern (replace names with your columns):

    SELECT SUM(
  CAST(col1 AS DECIMAL(30,10))
  + (CAST(col2 AS DECIMAL(30,12)) / CAST(col3 AS DECIMAL(30,12))) * CAST(col4 AS DECIMAL(30,10))
) AS total
FROM your_table;

    Using DECIMAL here keeps arithmetic decimal-exact and prevents cheap truncation at the division step.

  • Persist per-row totals in a DECIMAL column (computed column or update at insert/update). Then SUM the stored column. That avoids recomputing and re-rounding at query time and is standard for financial data.

  • If you prefer application-side control, fetch the raw fields and accumulate using arbitrary-precision math (PHP: BCMath or GMP). This eliminates any server-side rounding rules. Example pattern with BCMath:

    $total = '0';
while ($r = fetch_assoc($res)) {
  $row = bcadd($r['value1'], $r['value2'], 8);
  $row = bcadd($row, bcmul(bcdiv($r['value1'], $r['denom'], 12), $r['mult'], 8), 8);
  $total = bcadd($total, $row, 8);
}

Troubleshooting checklist: DESCRIBE the table to confirm types; run the expression per row (LIMIT 20) and compare to high‑precision PHP or a calculator; try SUM(CAST(expr AS DECIMAL(...))) with different scales to see when the discrepancy disappears.

This builds on ’s casting idea and /’s suggestions to move work outside a single SUM. For financial values, prefer DECIMAL with an explicitly chosen scale rather than DOUBLE.

Recommended Answers

All 13 Replies

Member Avatar for pritaeas

Are you referring to a custom function in PHP or the SUM() function in MySql ?

Member Avatar for freiheit

Are you referring to a custom function in PHP or the SUM() function in MySql ?

Im referring to the SUM() function in MySql , of course i could do that in php if this is necessary to solve my problem

Edited by freiheit because: n/a
Member Avatar for pritaeas

Can you give an example of you problem ?

Member Avatar for freiheit

Can you give an example of you problem ?

Ofcourse

I have a table , in each cell there are different numbers.
I use this query to make the sum of this mathematical formula withing the SUM brackets

SELECT SUM((kinisis_1c + kinisis_1e + (kinisis_1c / kinisis_1b)* kinisis_1a) * (1-fpa)*1.005)
From `kiniseis`
Where date BETWEEN '2006-10-03' AND '2007-01-01';

Ok the problem is that after 20-30 records , the sum is correct , but after that there is a high declination of the actual sum. (i tested that by hand to see the accuracy of the sum ).

So after i tried many things , i end up to the conclusion that the sum funcion rounds the ratio (kinisis_1c / kinisis_1b) , and causes the above problem of declination from the actual sum of those numbers.

Thats why i search a way to bypass this problem :( if there is any way ofcourse.

Any ideas would be appreciated

Member Avatar for Member #334542

you doing calculation directly in the query, so that I hope there is no need of SUM, try to implement the same calculation without SUM.

Member Avatar for pritaeas

All columns in the query are of the same data type ?

Member Avatar for freiheit

All columns in the query are of the same data type ?

All are decimal, and only colump `fpa` is double.

Any work around?

Member Avatar for farhan386

seems that you'll have to fetch each column and sum it one-by-one manually. You'll have greater control about these rounding problem, but it will surely slow down your program by a little bit.

Member Avatar for freiheit

seems that you'll have to fetch each column and sum it one-by-one manually. You'll have greater control about these rounding problem, but it will surely slow down your program by a little bit.

Hm what do you mean exactly,and how i should do that , my table has arround 1000 records.

Ofcourse if could be a way of summing cells that are withing a repeat region with also do the trick for me.So to bypass the mysql roundings.

Member Avatar for pritaeas

What happens to your result, if you CAST all columns to double.

Member Avatar for freiheit

What happens to your result, if you CAST all columns to double.

Hmm i tried that couple of minutes ago , no change. :(

Member Avatar for freiheit

Does anyone know if this issue is taking place also to mysql?

Member Avatar for pritaeas

I think your best bet is to wait for the answer from MySql. (Saw you posted there).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.