Re: Query skips one set of records

[Felix Geerinckx]

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