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:
member_id varchar(6)
member_sub_id varchar(6)
pay_method varchar(8)
monthly_cost decimal(11,0)
anniv_bill_date date
dtCreated date
fetch_date date
This query:
SELECT
member_id,
member_sub_id,
IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM(
monthly_cost * 12.00 ) ) ) AS Amount
FROM subinfo
WHERE
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
