>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.