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 '11 at 04:33 AM in Default

Bhuvans gravatar image

Bhuvans
220 17 19 21

Hey guys.. do we have any solution for the above scenario
Jun 06 '11 at 04:41 AM Bhuvans
@Bhuvans : have patience, you asked the question 8 minutes before
Jun 06 '11 at 04:46 AM Kev Riley ♦♦
@Bhuvans - adding 'please' somewhere would have helped too ...
Jun 06 '11 at 05:11 AM Fatherjack ♦♦
hey guys.. pardon me
Jun 06 '11 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 '11 at 04:57 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(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 '11 at 04:45 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 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 '11 at 04:54 AM Bhuvans

so the output should look like

5 30

1 row affected
Jun 06 '11 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 '11 at 05:00 AM Kev Riley ♦♦
yeah ur right.. but ur first one helped me... thank you
Jun 06 '11 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.

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.

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:

x111

asked: Jun 06 '11 at 04:33 AM

Seen: 508 times

Last Updated: Jun 06 '11 at 04:35 AM