CODEWITHSUNDEEP
sqlsql-server
Roger Steinberg
Roger Steinberg

Reputation: 1604

How do i count occurences that were joined SQL

I am joining two tables:

DECLARE @Temp TABLE (
id INT)

INSERT INTO @Temp
VALUES (5)
,(2)
,(3)

DECLARE @Temp2 TABLE (
member_id INT)

INSERT INTO @Temp2
VALUES (5)
,(1)
,(3)

How do i count the number of rows that can be LEFT joined and the ones that can't. In this example: 5 & 3 from @Temp can be joined to @Temp2 and only 2 from @Temp can't be joined. I would like my output to show the following:

+--------+------------+
| Joined | Not_Joined |
+--------+------------+
|      2 |          1 |
+--------+------------+

Upvotes: 0

Views: 64

Answers (3)

Himanshu
Himanshu

Reputation: 3970

Select count(*) as 'NOT Joined ',
 (Select t1.count(*) from table1 
 t1)-count(*) as 'Joined' 
 from table1 where id NOT IN (Select member_id from table2);

Its basically how a left join works that is Common values of both the tables plus the value of table 1 which doesnt exists in table 2.

Upvotes: -1

T McKeown
T McKeown

Reputation: 12857

The count from @Temp that EXISTS in @Temp2:

SELECT COUNT(*) FROM @TEMP WHERE ID IN(SELECT MEMBER_ID FROM @TEMP2)

The count from @Temp2 not in @Temp:

SELECT COUNT(*) FROM @TEMP2 WHERE MEMBER_ID NOT IN(ID FROM @TEMP)

Now to create a single result set, there are many ways but here is a simple one:

SELECT 
    (SELECT COUNT(*) FROM @TEMP2 WHERE MEMBER_ID IN(ID FROM @TEMP)) AS [Joined],
    (SELECT COUNT(*) FROM @TEMP WHERE ID NOT IN(SELECT MEMBER_ID FROM @TEMP2)) AS [NotJoined]

@Sean Lange's answer is more specific to the JOIN question, my answer simply counts what exists in the lists.

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

You can do this in a single query using COUNT and SUM. This should produce the results you are looking for. 

DECLARE @Temp TABLE (
id INT)

INSERT INTO @Temp
VALUES (5)
,(2)
,(3)

DECLARE @Temp2 TABLE (
member_id INT)

INSERT INTO @Temp2
VALUES (5)
,(1)
,(3)

select Joined = count(t2.Member_id)
    , NotJoined = sum(case when t2.Member_id is null then 1 end)
from @Temp t
left join @Temp2 t2 on t2.member_id = t.id

Upvotes: 2

Related Questions