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


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