Re: Query skips one set of records

[Miles Thompson]

At 03:58 AM 1/19/2007, Felix Geerinckx wrote:

>miles@stripped (Miles Thompson) wrote in
>news:6.1.1.1.0.20070118164903.03971d68@ style="color:#666">stripped:
>
>
> > 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.
>
>
>The monthly_cost column is indeterminate (read meaningless) outside of an
>aggregate function, since you're not grouping on it.
>See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html
>
>--
>felix

Felix,

Sorry, but I do not understand what you are trying to explain. I have 
looked at that page in the manual, but I'm no clearer on its meaning.

What I understand my SQL statement to say, is for every member_id add up 
the monthly cost, subject to this condition: If the monthly cost = 10 then 
calculate  monthly_cost *2 plus 200 and add it to the total for that 
member_id, otherwise calculate monthly_cost * 12 and add that to the total 
for the member_id.

How does this differ from the second example given in the previous section, 
http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ?

What would the point be on grouping on monthly_cost? Apart from that, it 
works just fine for all other member_id's.

If you could bring a little more light I would appreciate it.

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