At 03:58 AM 1/19/2007, Felix Geerinckx wrote:
>miles@stripped (Miles Thompson) wrote in
> > 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.
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,
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