>>>I see, that was just a typo, of course. BTW, why does he divide by 1000? Other than that I still don't see a difference.
>>
>>I think other than that they are the same. One of you groups by 1 the other by 2. I'm not sure if there's a difference. The GROUP BY clause still confuses me.
>>
>>The division by 1000 is mine not Cetin's. All the numbers in the report are presented in (000's). The expenses however are to the penny in the table so they need to be truncated. Maybe not the best way but it seems to work.
>
>Ah, I see. I group by 2 and he groups by 1 because I put the Group field in the second place and he in the first.
>
>These 2 queries were identical except for my typo. But Cetin used a good point of casting the group field to an integer. I'm not sure if this was necessary, but didn't hurt, of course.
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?