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