List:General Discussion« Previous MessageNext Message »
From:Miles Thompson Date:January 18 2007 9:26pm
Subject:Query skips one set of records
View as plain text  
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


Thread
Query skips one set of recordsMiles Thompson18 Jan
  • Re: Query skips one set of recordsDan Nelson19 Jan
    • Re: Query skips one set of recordsMiles Thompson21 Jan
  • Re: Query skips one set of recordsFelix Geerinckx19 Jan
    • Re: Query skips one set of recordsMiles Thompson21 Jan
      • Re: Query skips one set of recordsPeter Brawley21 Jan
        • Re: Query skips one set of recordsMiles Thompson21 Jan