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


Your conclusion is somewhat correct.....it could be restated as.

When using a Group By there can be only 2 types of fields in the result, the fields which are being aggregated and the fields that provides the "rollup".