The query displayed below performs flawlessly, except for these two records:
7364 M01740 002505 10 Invoice 2006-12-13 2006-12-13 2006-12-31
7365 M01740 002506 5 Invoice 2006-12-13 2006-12-13 2006-12-31
Here's the table structure:
IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM(
monthly_cost * 12.00 ) ) ) AS Amount
MONTH(anniv_bill_date) = 12 AND
MONTH(fetch_date) = 12 AND
YEAR(fetch_date) = 2006 AND
pay_method = 'Invoice'
GROUP BY member_id
Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 *
12 for the next one.
Instead it is returning $180.
Other records which have similar conditions are processed with no
difficulty. Is this kind of intermittency a bug in MySQL 3.23?
If anyone has any suggestions, then I would love to know it. If I execute a
test query without the SUM() function and GROUP BY, but using all of the
other WHERE conditions, these two records are flawlessly selected from the
data set. With SUM() and GROUP BY - wrong results, for only these two.
Suggestions or hints will be welcome.
I can now upgrade to MySQL 4.x, so I will do that.
Regards - Miles Thompson
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.16.14/636 - Release Date: 1/18/2007