masri999
3/20/2007 3:20:00 PM
On Mar 20, 4:44 pm, geoffa <geo...@discussions.microsoft.com> wrote:
> i want to group the results by 'descr' and sum the 'amount' column at each
> group. I can't quite figure out the script. any help would be appreciated.
> thank
>
> use myDB
> declare @mynum integer
> set @mynum = 136
> select company_id as comp, amount, batch_code as code, batch_number as
> batch, batch_seq as seq, descr, payee_id as payee
> from myTable
> where company_id = 'ABC' and batch_code = 'FUEL' and batch_number = @mynum
>
> order by batch_seq
>
> comp amount code batch seq descr
> payee
> -
> ABC 5.55 FUEL 136 0 NY018 - TURNER PAUL
> CHUTR
> ABC -3.80 FUEL 136 1 NY018 - TURNER PAUL
> CHUTR
> ABC 5.32 FUEL 136 2 OH902 - LOBDER RODNE
> CHUTR
> ABC -3.57 FUEL 136 3 OH902 - LOBDER RODNE
> CHUTR
> ABC 4.64 FUEL 136 4 PA082 - GALLATIN DAN
> CHUTR
> ABC -2.89 FUEL 136 5 PA082 - GALLATIN DAN
> CHUTR
> ABC 5.51 FUEL 136 6 IL388 - MCDONALD GEO
> CHUTR
> ABC -3.76 FUEL 136 7 IL388 - MCDONALD GEO
> CHUTR
>
> i want it to look look like this....
>
> ABC 5.55 FUEL 136 0 NY018 - TURNER PAUL
> CHUTR
> ABC -3.80 FUEL 136 1 NY018 - TURNER PAUL
> CHUTR
> 1.75
>
> ABC 5.32 FUEL 136 2 OH902 - LOBDER RODNE
> CHUTR
> ABC -3.57 FUEL 136 3 OH902 - LOBDER RODNE
> CHUTR
> 1.75
>
> ABC 4.64 FUEL 136 4 PA082 - GALLATIN DAN
> CHUTR
> ABC -2.89 FUEL 136 5 PA082 - GALLATIN DAN
> CHUTR
> 1.75
>
> ABC 5.51 FUEL 136 6 IL388 - MCDONALD GEO
> CHUTR
> ABC -3.76 FUEL 136 7 IL388 - MCDONALD GEO
> CHUTR
> 1.75
select company_id as comp, amount, batch_code as code, batch_number
as
batch, batch_seq as seq, descr, payee_id as payee
from myTable
where company_id = 'ABC' and batch_code = 'FUEL' and batch_number =
@mynum
union all
select 0 as comp, sum(amount) as amount , 0 as code, 0 as
batch, 999999 as seq, descr, 0 as payee
from myTable
where company_id = 'ABC' and batch_code = 'FUEL' and batch_number =
@mynum
group by descr
order by descr,seq
For total row batch_seq is coded with dummy value 999999