Inner join 2 table and sum a column

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

General SQL Server Forums

New to SQL Server Programming

Inner join 2 table and sum a column

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-11-15 : 22:41:27

Hi admin and assistant of forum,

i have 2 table.

1st ItemTable1 (ProductId + CustomerServiceID as the primary key)
ProductId CustomerServiceID value1 value2 ..... value12
--------------------------------------------------------
P1 C1 1 1 ..... 1
P1 C2 2 2 ..... 2
P2 C3 3 3 ..... 3
P2 C1 4 4 ..... 4

1st ItemTable2(ProductId + CustomerServiceID as the primary key)
ProductId CustomerServiceID Totalvalue
--------------------------------------------------------
P1 C1 10
P1 C1 20
P1 C2 20
P2 C1 10
P2 C1 10
P2 C3 10

What i want is Query * from ItemTable1 and Sum(Totalvalue) from ItemTable2 as below.

ProductId CustomerServiceID value1 value2 .. value12 Sum(Totalvalue)
--------------------------------------------------------
P1 C1 1 1 .. 1 (10+20=30)
P1 C2 2 2 .. 2 (20)
P2 C1 4 4 .. 4 (10+10=20)
P2 C3 3 3 .. 3 (10)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-16 : 03:35:13

Try like this

select t1.*,tot_val from temTable1 t1 inner join
(Select productid,CustomerServiceID,sum(Totalvalue) as tot_val from
ItemTable2 group by productid,CustomerServiceID) t on t1.productid=t.productid
and t1.CustomerServiceID=t.CustomerServiceID

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-11-16 : 20:38:10

[quote]Originally posted by senthil_nagore

Try like this

select t1.*,tot_val from temTable1 t1 inner join
(Select productid,CustomerServiceID,sum(Totalvalue) as tot_val from
ItemTable2 group by productid,CustomerServiceID) t on t1.productid=t.productid
and t1.CustomerServiceID=t.CustomerServiceID

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Thanks senthil_nagore.
It is what i want, Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources