Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with summing up results

Status
Not open for further replies.

awaria

IS-IT--Management
Sep 16, 2010
100
US
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
 
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
 
I had already tried that and didn't work.
The results were the same rendering the first 25 vendorIDs
 
You're right.

I misinterpreted your recommendation.

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

Many Thanks,

Andrew
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top