Count from two tables. - SQL Server Forums

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

Count from two tables.

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-10-10 : 08:24:22

What to know if it can be done.

Have a main table with the user id's in it it and i want to join it to two other tables. One being a downloads table and the second being a searches tables, will join them both via the user id's. What i would like to do is count the rows from both the and searches tables and return each value next with userid.

e.g

User ID, Downloads, Searches

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-10 : 08:32:26

Just to clarify , is that the counts as part of the INNER JOIN or do you want the FULL count?
Do you want a DISTINCT value count?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

lemondash
Posting Yak Master

159 Posts

Posted - 2007-10-10 : 08:38:46

I want the count of how many times that the user Id are in each of the relative tables.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-10 : 09:09:31

i may be over complicating this but it's been a long day...

declare @down table (userid int)

insert into @down
select 1 union all
select 2 union all
select 2

declare @search table (userid int)

insert into @search
select 1 union all
select 1 union all
select 3

select * from @down
select * from @search

select coalesce(d.userid,s.userid) as userid,downs,searches
from (select userid,count(userid) as downs from @down group by userid) d
full join (select userid, count(userid)as searches from @search group by userid) s
on s.userid = d.userid

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 09:21:02

[code]select userid,
sum(case when tablename = '@down' then 1 else 0 end) AS [@down],
sum(case when tablename = '@search' then 1 else 0 end) AS [@search]
from (
SELECT userid,'@down' as tablename from @down union all
SELECT userid,'@search' from @search
) AS f
group by userid[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 09:42:49

Even easier ...


select		userid,
		sum([@Down])) AS [@down],
		sum([@Search]) AS [@search]
from		(
			SELECT	userid,1 as [@down], 0 as [@search] from @down union all
			SELECT	userid,0,1 from @search
		) AS f
group by	userid

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources