Select statements in one table 2

[ShawnMolloy]

how do i combine these select statements into one table?

select count(*) from wt_message MessageCount where recipient=1
select count(*) from wtcomment commentCount where userid=1


Thanks,



Shawn Molloy
Seattle, WA

 

[jbenson001]

You can use UNION or UNION ALL to combine them

 

[ShawnMolloy]

A union on this code puts both of the values in two seperate rows in one single column with no name.

I'd like the data to be in a table like this:

MessageCount | CommentCount
----------------------------
0 | 7


Unless I'm using the union statement incorrectly....

Shawn Molloy
Seattle, WA

 

[jbenson001]

To put it on one row you will have to run each select and insert the value into the appropriate column

 

[ShawnMolloy]

Do you mean I have to create variables to hold each select result then add it to a temp table? can you explain a littler further please?

Thanks for your help!

Shawn Molloy
Seattle, WA

 

[jbenson001]

Sorry I haven't gotten back to you... just haven't had the time.. try this. .it worked for me..

declare @cnt1 int
declare @cnt2 int
set @cnt1 = (select count(*) from wt_message MessageCount where recipient=1)
set @cnt2 = (select count(*) from wtcomment commentCount where userid=1)

select @cnt1 as MessageCount , @cnt2 as CommentCount into #test

select * from #test

drop table #test

Jim

 

[ShawnMolloy]

Awesome - this works perfectly! Thanks for your help again Jim.

Shawn Molloy
Seattle, WA

 

[SQLDenis]

you don't need to put this into a variable

select (select count(*) from wt_message MessageCount where recipient=1) as MessageCount,
(select count(*) from wtcomment commentCount where userid=1) as CommentCount

example

use pubs
go

select (select count(*) from authors) as MessageCount,
(select count(*) from authors) as CommentCount

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[jbenson001]

no problem glad to help...

and Denis' way works well too