At 12:14 AM 1/19/2007, Dan Nelson wrote:
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
[EMAIL PROTECTED]
Dan,
I thought I had proofed the message carefully when I sent it - your results
are correct, but I should have left off the column containing the 7364 and
7365. Those are auto incrementing primary keys. The grouping, and summing,
is to be done on the "MO1740" column.
I upgraded to MySQL 4.1 and results remained the same.
Thank you for looking at this.
Regards - Miles
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
