x

small prob in join?

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

avatar image

Bhuvans
220 19 19 24

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
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

avatar image

Kevin Feasel
6.2k 4 7 15

(comments are locked)
10|1200 characters needed characters left

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 requirements

Try 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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

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 30

1 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
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x148

asked: Jun 06, 2011 at 04:33 AM

Seen: 679 times

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

Copyright 2016 Redgate Software. Privacy Policy