Need help with summing up results

[awaria]

My query is suppose to have the results sum by vendorID, however,
I'm obviously doing saoomethng wrong.

The PM30200 is the transaction table and the PM00200 is the Vendor Master table. I only need the PM00200 table for the Vendor Name.

Here is my query and below is a sample of the results:
--
SELECT top 25 b.vendorid, b.vendname,sum(a.docamnt) as [Spend]
FROM PM30200 a
inner join pm00200 b
on a.vendorid = b.vendorid
WHERE a.DOCDATE BETWEEN '2010-06-01 00:00:00.000' AND '2011-05-31 00:00:00.000'
and a.doctype = '6'
and substring(a.pordnmbr,2,1) <> 'c'
group by b.vendorid, b.vendname,a.docamnt
order by a.docamnt desc
--
VendorID Vendor Name Spend
010101 abc, inc 100.00
010101 abc, inc 20.00
454545 USA Co 400.00
010101 abc, inc 125.00

Results are summing by trx, not summing in total by vendorID.

Appreciate your assistance.

Thanks,

Andrew

 

[Alan0568]

take the docamnt out of the group by

 

[awaria]

When I do that, it does seem to sum, however, I'm getting the top 25 in alpha VendorID order, not the top 25 in most SPEND.

What needs to change to accomlpish this?

Thanks for the firdt tip as removing the sum of docamnt did remove the the summing by trx.

Thanks again,

Andrew

 

[Alan0568]

change the order to

order by sum(a.docamnt) desc

 

[awaria]

I had already tried that and didn't work.
The results were the same rendering the first 25 vendorIDs

 

[markros]

It should work correctly. Can you show the fixed query?

PluralSight Learning Library

 

[awaria]

You're right.

I misinterpreted your recommendation.

I reread and re-edited and now have the desired results.

Many Thanks,

Andrew