MySQL Forums
Forum List  »  Quality Assurance

Sum Function Doesn't Return Sum Of Values
Posted by: Jeffrey Haas
Date: May 17, 2011 10:14AM

Greetings,

We upgraded from MySQL version 4.0.16 (Windows) to version 5.0.77 (Linux) on Friday, and everything seems to be working fine with one exception.

We have two tables - TableA and TableB, both with various fields.

TableA: Contains fields "RefNum" (String), Unicode (String) and "DollarValue" (Double)
TableB: Contains fields "RefNum" (String) and "BatchNumber" (Integer)

TableA:
RefNum / Unicode / DollarValue
A / A1 / 5
A / A2 / 5
A / A3 / 5
A / A4 / 5
B / B1 / 10
B / B2 / 10

TableB:
RefNum / BatchNumber
A / 141
B / 141

For each RefNum, the values in DollarValue will all be the same (i.e. All "A" RefNum values will have a DollarValue of "5", all "B" RefNum values will have a DollarValue of "10", etc)

So, with this in mind, I'm writing a query that, given a specific Batch Number, I was to find the Sum of the DollarValue for each RefNum. In the above table I pass in a BatchNumber of 141, and the query should return a value of 15 ("A" = 5, "B" = 10).

The query I used in MySQL version 4 looked like this:

SELECT DISTINCT SUM(TableA.DollarValue) As MyDollarValue
FROM TableB INNER JOIN TableA ON TableB.RefNum = TableB.RefNum
WHERE TableB.BatchNumber = 141
GROUP BY TableB.RefNum

and the resulting "MyDollarValue" value would be 15.

Since the upgrade to MySQL version 5.0.77, the same query returns a "MyDollarValue" value of 40, which is the sum of the "DollarValue" value for every row, not the distinct rows returned when grouped by "RefNum".

Now, what makes this curious (and why I'm posting it in the bug forum) is that if I remove the SUM functionality from the query like so:

SELECT DISTINCT TableA.DollarValue
FROM TableB INNER JOIN TableA ON TableB.RefNum = TableB.RefNum
WHERE TableB.BatchNumber = 141
GROUP BY TableB.RefNum

the result is two rows: "5" and "10". With the SUM added, the resulting value (the SUM of "DollarValue") should be the sum of the rows returned without the SUM added, in this case the sum of 5 + 10, or 15. What gets returned instead is 5 + 5 + 5 + 5 + 10 + 10, or 40.

Is this a bug in this version of MySQL or am I doing something horribly wrong? Any help with this problem would be GREATLY appreciated.

Very best regards and thanks in advance,

Jeff

Options: ReplyQuote


Subject
Views
Written By
Posted
Sum Function Doesn't Return Sum Of Values
2264
May 17, 2011 10:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.