Syntax for multiple table sum - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Syntax for multiple table sum

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2005-03-08 : 15:11:59

Hi folks,

I just can't get the syntax correctly but it should be fairly simple. I have 2 tables, one which contains the ClientID and Purchases, then other contains ClientID and DeliveryCost. I would like to get a result set which contains :

ClientID, Sum(Purchases), Sum(DeliveryCost) Group by ClientID

I figure that it involves a join statement but I can't figure out how to write it.

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 15:14:00

select t1.ClientID, Sum(t1.Purchases), Sum(t2.DeliveryCost)
from table1 t1 join table2 t2 on t1.ClientID = t2.ClientID
Group by t1.ClientID

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-08 : 15:24:35

what is the primary key of each of those tables? If the common connection between them is ClientID, then you need to summarize each of them first in derived tables by ClientID and then join them together -- you can't simply join two transaction tables together, the rows will cross join each other.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 15:36:46

DOH!!!... of course... thanx jeff.


select 	t1.ClientID, t1.Purchases, t2.DeliveryCost
from 	(select ClientID, sum(Purchases) as Purchases
		from table1 t1 group by ClientID) t1
	join 
	(select ClientID, sum(DeliveryCost) as DeliveryCost 
		from table2 group by ClientID)t2 
	on t1.ClientID = t2.ClientID

Go with the flow & have fun! Else fight the flow

WindChaser
Posting Yak Master

225 Posts

Posted - 2005-03-08 : 16:41:17

Whoooa, I was sooooooo close! It works perfectly. Thanks guys!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources