>>If you will indulge me a little longer perhaps I can learn a bit about the Group By clause . . .
>>Taking just the subquery, the following works

>>select cast(val(substr(sponsor_id,5,2)) as int) as sponsor_id, ;
>>    sum(amount) as amount ;
>>    from tip ;
>>    inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ;
>>    group by 1
>>

>>This also works

>>select tip.tip_id, ;
>>    sum(amount) as amount ;
>>    from tip ;
>>    inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ;
>>    group by 1

>>but this does not.

>>select tip.tip_id, cast(val(substr(sponsor_id,5,2)) as int) as sponsor_id,;
>>    sum(amount) as amount ;
>>    from tip ;
>>    inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ;
>>    group by 1

>>From this I conclude that when using a Group By there can be only 2 fields in the result, the field which is being aggregated and the field that provides the "rollup". Would that be a correct conclusion?
>
>Don,
>
>The conclusion is incorrect :) There could be any number of fields in the result. The rule is very simple - all fields that are not in GROUP by should be used in any of the aggregate functions - AVG, MAX, MIN, COUNT, SUM.
>
>In other words,
>
>select tip.tip_id, cast(val(substr(sponsor_id,5,2)) as int) as sponsor_id,;
>> sum(amount) as amount ;
>> from tip ;
>> inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ;
>> group by 1,2
>
>will work (note, I added 2 here to group on it).
>
>There should be a good explanation about this in behavioral change section in VFP Help.

That's helpful. Thanks.