Group by sum (Access 2000)

Topic

New Reply

These are the sales that I have for this customer:
GPRNT EXTENDEDPR
56325 -71.88
56325 71.76
56325 -95.88
56325 -71.76
56325 95.88
56325 71.88

If I add their sales up, I get zero.

When I do a Group By Qry (Group by GPRNT and Sum EXTENDEDPR) I get this strange number.
GPRNT SumOfEXTENDEDPR
56325 -1.4210854715202E-14

Why isn’t it zero?

Reply | Quote

Replies

Hi,
I created two tables (tblCustomer and tblSales) and populated them with your data. Then I created a GroupBy query with the following:

SELECT DISTINCTROW tblCustomer.GPRNT, Sum(tblSales.EXTENDEDPR) AS [Sum Of EXTENDEDPR]
FROM tblCustomer INNER JOIN tblSales ON tblCustomer.GPRNT = tblSales.GPRNT
GROUP BY tblCustomer.GPRNT;

This returns a total of zero as you expected it to. Does this help?

Reply | Quote

Hi,
I created two tables (tblCustomer and tblSales) and populated them with your data. Then I created a GroupBy query with the following:

SELECT DISTINCTROW tblCustomer.GPRNT, Sum(tblSales.EXTENDEDPR) AS [Sum Of EXTENDEDPR]
FROM tblCustomer INNER JOIN tblSales ON tblCustomer.GPRNT = tblSales.GPRNT
GROUP BY tblCustomer.GPRNT;

This returns a total of zero as you expected it to. Does this help?

Reply | Quote

It’s a matter of rounding. We use the decimal system to write down numbers; computers use the binary system for storing numbers and for calculations. A number such as 71.88 can’t be represented exactly as a binary number with finite precision, so there is a tiny rounding error. And I suspect that there are extremeIn your case, the rounding errors add up to the extremely small number -1.4210854715202E-14, that is -1.421… times 10 to the power -14, or -0.00000000000001421… So there is nothing to worry about. You can format the result as Fixed or Standard or Currency, with 2 decimals. The infinitesimal rounding error will be invisible then.

But, like wrksmrt, I can’t reproduce your result. Are the sales figures the result of calculations, or have they been imported from another application?

Reply | Quote

It’s a matter of rounding. We use the decimal system to write down numbers; computers use the binary system for storing numbers and for calculations. A number such as 71.88 can’t be represented exactly as a binary number with finite precision, so there is a tiny rounding error. And I suspect that there are extremeIn your case, the rounding errors add up to the extremely small number -1.4210854715202E-14, that is -1.421… times 10 to the power -14, or -0.00000000000001421… So there is nothing to worry about. You can format the result as Fixed or Standard or Currency, with 2 decimals. The infinitesimal rounding error will be invisible then.

But, like wrksmrt, I can’t reproduce your result. Are the sales figures the result of calculations, or have they been imported from another application?

Reply | Quote