# small prob in join?

 0 My First table has `````` resid mId sum ----- ----- ---- a 2 10 b 2 10 a 2 10 b 3 20 c 3 20 `````` My second table has ``````mid val --- ---- 2 10 3 20 `````` but i failed to display like this count (resid) from first table, sum(val) from only sec table --> where cond is mid in (2,3)When i tried to join i am getting the sum of all the columns in first table more ▼ asked Jun 06, 2011 at 04:33 AM in Default Bhuvans 220 ● 19 ● 19 ● 21 Kev Riley ♦♦ 54.1k ● 47 ● 49 ● 76 Hey guys.. do we have any solution for the above scenario Jun 06, 2011 at 04:41 AM Bhuvans @Bhuvans : have patience, you asked the question 8 minutes before Jun 06, 2011 at 04:46 AM Kev Riley ♦♦ @Bhuvans - adding 'please' somewhere would have helped too ... Jun 06, 2011 at 05:11 AM Fatherjack ♦♦ hey guys.. pardon me Jun 06, 2011 at 05:22 AM Bhuvans add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 My answer is very similar to @Kev Riley's (so much so that I didn't post it once I saw his...), but with your latest requirement, here goes: ``````declare @t1 table ( resid char(1), mid tinyint, s int ) declare @t2 table ( mid tinyint, val int ) insert into @t1(resid, mid, s) values ('a', 2, 10), ('b', 2, 10), ('a', 2, 10), ('b', 3, 20), ('c', 3, 20); insert into @t2(mid, val) values (2, 10), (3, 20); select sum(r.resids) as numResids, SUM(t2.val) as summation from @t2 t2 inner join (select mid, COUNT(resid) as resids from @t1 group by mid) r on r.mid = t2.mid where t2.mid in (2, 3); ``````The load statements are specific to SQL 2008 and later, but the select statement (which is the important part here) will work in previous editions. more ▼ answered Jun 06, 2011 at 04:57 AM Kevin Feasel 6.1k ● 3 ● 5 ● 11 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 If the second table already has the sum(val) aggregated by mId, then you need to join differently ``````declare @MyFirstTable table (resid char(1), mId int, [sum] int) insert into @MyFirstTable select 'a',2,10 insert into @MyFirstTable select 'b',2,10 insert into @MyFirstTable select 'a',2,10 insert into @MyFirstTable select 'b',3,20 insert into @MyFirstTable select 'c',3,20 declare @MySecondTable table (mId int, val int) insert into @MySecondTable select 2,10 insert into @MySecondTable select 3,20 select firsttable.countval, secondtable.val from ( select mId, COUNT(resid) as countval from @MyFirstTable where mId in (2,3) group by mId ) firsttable join @MySecondTable secondtable on firsttable.mId = secondtable.mId ``````gives ``````countval val ----------- ----------- 3 10 2 20 (2 row(s) affected) `````` Edit : OP has clarified the output requirementsTry this ``````select ( select COUNT(resid) as countval from @MyFirstTable where mId in (2,3) ), ( select SUM(val) from @MySecondTable where mId in (2,3) `````` ) more ▼ answered Jun 06, 2011 at 04:45 AM Kev Riley ♦♦ 54.1k ● 47 ● 49 ● 76 hi .. but i need the output like this 5( 2 and 3 count) 30 ( 2 annd 3 val sum from first table)so the output should look like 5 30 Jun 06, 2011 at 04:54 AM Bhuvans so the output should look like 5 301 row affected Jun 06, 2011 at 04:55 AM Bhuvans have updated my answer to give you your output - this doesn't use a join, but 2 sub selects - not sure how to 'join' these 2 datasets as they are simply 2 aggregates presented side-by-side Jun 06, 2011 at 05:00 AM Kev Riley ♦♦ yeah ur right.. but ur first one helped me... thank you Jun 06, 2011 at 05:20 AM Bhuvans add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x123

asked: Jun 06, 2011 at 04:33 AM

Seen: 572 times

Last Updated: Jun 06, 2011 at 04:35 AM