[Resolved] Getting a total of all transactions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Getting a total of all transactions

Author

Topic

jonathanr
Starting Member

11 Posts

Posted - 2011-01-26 : 20:25:50

Hi,

The situation is as follows:
We have several vendors that we purchase from, so over a given period we may purchase from a particular vendor multiple times or only once. I need to construct a query that sums up to a total amount for each vendor and then show only the vendors that have totals over a certain amount.

For example: There are several vendors (below) and show all vendors that have a total of greater than $200

1/1/2009 Joe Blogs and Co. $50
3/1/2009 Penny Savings supplies $200
13/1/2009 Joe Blogs and Co. $500
8/1/2009 Penny Savings supplies $300
9/1/2009 OTT Inc. $100

Result:

Joe Blogs and Co. $550
Penny Savings supplies $500

This is my query so far:

select
pmt_ref_date,
vendor_id,
vendor_name,
bkg_amt,
pmt_status_code
from dbo.pmt
where pmt_status_code ='CLR'
and pmt_ref_date between x AND y

Can anyone point me in the direction as tio how I can achieve what I am after?

Thanks,

Jonathanr

ZZartin
Starting Member

30 Posts

Posted - 2011-01-26 : 20:41:56

Try using a group by clause to generate a sum on the field you want to get the total from, then you can use a HAVING clause at the end of statement to only include records that meet your total.

jonathanr
Starting Member

11 Posts

Posted - 2011-01-26 : 20:53:09

Hi ZZartin,

Thanks, I have come up with this so far:

se DBSpymt
SELECT vendor_name,SUM(bkg_amt) AS Total FROM dbo.pmt
where pmt_status_code ='CLR'
and pmt_ref_date between '2010/11/01'AND '2011/02/6'

GROUP BY vendor_name
order by Total desc

The only thing that I still need to do is the showing of totals over a specific amount. I will chech out the HAVING option..

jonathanr

jonathanr
Starting Member

11 Posts

Posted - 2011-01-26 : 20:55:01

Cool - all done :)

use DBSpymt
SELECT vendor_name,SUM(bkg_amt) AS Total FROM dbo.pmt
where pmt_status_code ='CLR'
and pmt_ref_date between '2010/11/01'AND '2011/02/6'
GROUP BY vendor_name
HAVING SUM(bkg_amt)<20000
order by Total desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources