Re: Query skips one set of records - Miles Thompson - com.mysql.lists.mysql

7 messages in com.mysql.lists.mysqlRe: Query skips one set of records

From	Sent On	Attachments
Miles Thompson	18 Jan 2007 13:26
Dan Nelson	18 Jan 2007 20:14
Felix Geerinckx	18 Jan 2007 23:58
Miles Thompson	21 Jan 2007 10:44
Miles Thompson	21 Jan 2007 11:01
Peter Brawley	21 Jan 2007 11:57
Miles Thompson	21 Jan 2007 12:42

Subject:	Re: Query skips one set of records
From:	Miles Thompson (mil...@allnovascotia.com)
Date:	01/21/2007 11:01:56 AM
List:	com.mysql.lists.mysql

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

mil...@allnovascotia.com (Miles Thompson) wrote in news:6.1....@pop.landlordit.com:

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,

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