List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 19 2007 4:14am
Subject:Re: Query skips one set of records
View as plain text  
In the last episode (Jan 18), Miles Thompson said:
> 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.

Not for me:

mysql> create table subinfo ( 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);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into subinfo values 
  ("7364","M01740","Invoice","10","2006-12-13","2006-12-13","2006-12-31"),
  ("7365","M01740","Invoice","5","2006-12-13","2006-12-13","2006-12-31");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 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;
+-----------+---------------+--------+
| member_id | member_sub_id | Amount |
+-----------+---------------+--------+
| 7364      | M01740        | 220.00 |
| 7365      | M01740        |  60.00 |
+-----------+---------------+--------+
2 rows in set (0.10 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.58   |
+-----------+
1 row in set (0.00 sec)

I get the same result on 5.1.14, too.  Try selecting count(*) along
with the other columns in your query and verify that another record
isn't sneaking in and getting totalled up.

-- 
	Dan Nelson
	dnelson@stripped
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