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 |

+--------+------------+

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

You would use COUNT or conditional SUM.

– Sean Lange
Nov 19 '18 at 21:22

add a comment |

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)

+--------+------------+

| Joined | Not_Joined |

+--------+------------+

|      2 |          1 |

+--------+------------+

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

You would use COUNT or conditional SUM.

– Sean Lange
Nov 19 '18 at 21:22

add a comment |

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)

+--------+------------+

| Joined | Not_Joined |

+--------+------------+

|      2 |          1 |

+--------+------------+

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

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)

+--------+------------+

| Joined | Not_Joined |

+--------+------------+

|      2 |          1 |

+--------+------------+

sql sql-server

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

asked Nov 19 '18 at 21:20

Roger Steinberg

1118

You would use COUNT or conditional SUM.

– Sean Lange
Nov 19 '18 at 21:22

add a comment |

You would use COUNT or conditional SUM.

– Sean Lange
Nov 19 '18 at 21:22

You would use COUNT or conditional SUM.

– Sean Lange
Nov 19 '18 at 21:22

add a comment |

3 Answers
3

active

oldest

votes

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

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

YUP thats exactly what i was looking for thank you!! why is my question downgraded?

– Roger Steinberg
Nov 19 '18 at 21:33

I up voted this too.. my lonely answer needs points...

– T McKeown
Nov 19 '18 at 21:34

Not my downvote but my guess is because you didn't show that you tried something. Seems pretty harsh to me. I thought your question was fine. And great job posting data and desired results!!

– Sean Lange
Nov 19 '18 at 21:35

thanks @SeanLange, i have the article and sources you posted on my last post in my bookmarks ;)

– Roger Steinberg
Nov 19 '18 at 21:59

add a comment |

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.

edited Nov 19 '18 at 21:28

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

thats equally good as the answer above. nice to know alternatives. thank you

– Roger Steinberg
Nov 19 '18 at 21:33

add a comment |

-1

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.

edited Nov 19 '18 at 21:48

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

The OP put in the effort to provide sample data and a nice consumable format. This as posted has several syntax errors. The OP deserves better.

– Sean Lange
Nov 19 '18 at 21:38

Can anybody explain why i have got a downvote.

– Himanshu Ahuja
Nov 19 '18 at 21:39

Because your code doesn't work as posted. If you fix the code so it works it would be considerably better.

– Sean Lange
Nov 19 '18 at 21:40

@Sean Lange Can you please check it now

– Himanshu Ahuja
Nov 19 '18 at 21:50

All you did was format it. There are still syntax errors. And since the OP posted such nice consumable data it really should work with what they posted.

– Sean Lange
Nov 20 '18 at 14:04

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382801%2fhow-do-i-count-occurences-that-were-joined-sql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

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

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

YUP thats exactly what i was looking for thank you!! why is my question downgraded?

– Roger Steinberg
Nov 19 '18 at 21:33

I up voted this too.. my lonely answer needs points...

– T McKeown
Nov 19 '18 at 21:34

Not my downvote but my guess is because you didn't show that you tried something. Seems pretty harsh to me. I thought your question was fine. And great job posting data and desired results!!

– Sean Lange
Nov 19 '18 at 21:35

thanks @SeanLange, i have the article and sources you posted on my last post in my bookmarks ;)

– Roger Steinberg
Nov 19 '18 at 21:59

add a comment |

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

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

YUP thats exactly what i was looking for thank you!! why is my question downgraded?

– Roger Steinberg
Nov 19 '18 at 21:33

I up voted this too.. my lonely answer needs points...

– T McKeown
Nov 19 '18 at 21:34

Not my downvote but my guess is because you didn't show that you tried something. Seems pretty harsh to me. I thought your question was fine. And great job posting data and desired results!!

– Sean Lange
Nov 19 '18 at 21:35

thanks @SeanLange, i have the article and sources you posted on my last post in my bookmarks ;)

– Roger Steinberg
Nov 19 '18 at 21:59

add a comment |

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

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

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

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

answered Nov 19 '18 at 21:26

Sean Lange

24.5k21735

YUP thats exactly what i was looking for thank you!! why is my question downgraded?

– Roger Steinberg
Nov 19 '18 at 21:33

I up voted this too.. my lonely answer needs points...

– T McKeown
Nov 19 '18 at 21:34

Not my downvote but my guess is because you didn't show that you tried something. Seems pretty harsh to me. I thought your question was fine. And great job posting data and desired results!!

– Sean Lange
Nov 19 '18 at 21:35

thanks @SeanLange, i have the article and sources you posted on my last post in my bookmarks ;)

– Roger Steinberg
Nov 19 '18 at 21:59

add a comment |

YUP thats exactly what i was looking for thank you!! why is my question downgraded?

– Roger Steinberg
Nov 19 '18 at 21:33

I up voted this too.. my lonely answer needs points...

– T McKeown
Nov 19 '18 at 21:34

Not my downvote but my guess is because you didn't show that you tried something. Seems pretty harsh to me. I thought your question was fine. And great job posting data and desired results!!

– Sean Lange
Nov 19 '18 at 21:35

thanks @SeanLange, i have the article and sources you posted on my last post in my bookmarks ;)

– Roger Steinberg
Nov 19 '18 at 21:59

YUP thats exactly what i was looking for thank you!! why is my question downgraded?

– Roger Steinberg
Nov 19 '18 at 21:33

I up voted this too.. my lonely answer needs points...

– T McKeown
Nov 19 '18 at 21:34

Not my downvote but my guess is because you didn't show that you tried something. Seems pretty harsh to me. I thought your question was fine. And great job posting data and desired results!!

– Sean Lange
Nov 19 '18 at 21:35

thanks @SeanLange, i have the article and sources you posted on my last post in my bookmarks ;)

– Roger Steinberg
Nov 19 '18 at 21:59

add a comment |

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.

edited Nov 19 '18 at 21:28

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

thats equally good as the answer above. nice to know alternatives. thank you

– Roger Steinberg
Nov 19 '18 at 21:33

add a comment |

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.

edited Nov 19 '18 at 21:28

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

thats equally good as the answer above. nice to know alternatives. thank you

– Roger Steinberg
Nov 19 '18 at 21:33

add a comment |

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.

edited Nov 19 '18 at 21:28

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

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.

edited Nov 19 '18 at 21:28

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

edited Nov 19 '18 at 21:28

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

answered Nov 19 '18 at 21:23

T McKeown

11.5k11727

thats equally good as the answer above. nice to know alternatives. thank you

– Roger Steinberg
Nov 19 '18 at 21:33

add a comment |

thats equally good as the answer above. nice to know alternatives. thank you

– Roger Steinberg
Nov 19 '18 at 21:33

thats equally good as the answer above. nice to know alternatives. thank you

– Roger Steinberg
Nov 19 '18 at 21:33

add a comment |

-1

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.

edited Nov 19 '18 at 21:48

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

The OP put in the effort to provide sample data and a nice consumable format. This as posted has several syntax errors. The OP deserves better.

– Sean Lange
Nov 19 '18 at 21:38

Can anybody explain why i have got a downvote.

– Himanshu Ahuja
Nov 19 '18 at 21:39

Because your code doesn't work as posted. If you fix the code so it works it would be considerably better.

– Sean Lange
Nov 19 '18 at 21:40

@Sean Lange Can you please check it now

– Himanshu Ahuja
Nov 19 '18 at 21:50

All you did was format it. There are still syntax errors. And since the OP posted such nice consumable data it really should work with what they posted.

– Sean Lange
Nov 20 '18 at 14:04

add a comment |

-1

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.

edited Nov 19 '18 at 21:48

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

The OP put in the effort to provide sample data and a nice consumable format. This as posted has several syntax errors. The OP deserves better.

– Sean Lange
Nov 19 '18 at 21:38

Can anybody explain why i have got a downvote.

– Himanshu Ahuja
Nov 19 '18 at 21:39

Because your code doesn't work as posted. If you fix the code so it works it would be considerably better.

– Sean Lange
Nov 19 '18 at 21:40

@Sean Lange Can you please check it now

– Himanshu Ahuja
Nov 19 '18 at 21:50

All you did was format it. There are still syntax errors. And since the OP posted such nice consumable data it really should work with what they posted.

– Sean Lange
Nov 20 '18 at 14:04

add a comment |

-1

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.

edited Nov 19 '18 at 21:48

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

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.

edited Nov 19 '18 at 21:48

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

edited Nov 19 '18 at 21:48

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

answered Nov 19 '18 at 21:35

Himanshu Ahuja

658216

The OP put in the effort to provide sample data and a nice consumable format. This as posted has several syntax errors. The OP deserves better.

– Sean Lange
Nov 19 '18 at 21:38

Can anybody explain why i have got a downvote.

– Himanshu Ahuja
Nov 19 '18 at 21:39

Because your code doesn't work as posted. If you fix the code so it works it would be considerably better.

– Sean Lange
Nov 19 '18 at 21:40

@Sean Lange Can you please check it now

– Himanshu Ahuja
Nov 19 '18 at 21:50

All you did was format it. There are still syntax errors. And since the OP posted such nice consumable data it really should work with what they posted.

– Sean Lange
Nov 20 '18 at 14:04

add a comment |

The OP put in the effort to provide sample data and a nice consumable format. This as posted has several syntax errors. The OP deserves better.

– Sean Lange
Nov 19 '18 at 21:38

Can anybody explain why i have got a downvote.

– Himanshu Ahuja
Nov 19 '18 at 21:39

Because your code doesn't work as posted. If you fix the code so it works it would be considerably better.

– Sean Lange
Nov 19 '18 at 21:40

@Sean Lange Can you please check it now

– Himanshu Ahuja
Nov 19 '18 at 21:50

All you did was format it. There are still syntax errors. And since the OP posted such nice consumable data it really should work with what they posted.

– Sean Lange
Nov 20 '18 at 14:04

The OP put in the effort to provide sample data and a nice consumable format. This as posted has several syntax errors. The OP deserves better.

– Sean Lange
Nov 19 '18 at 21:38

Can anybody explain why i have got a downvote.

– Himanshu Ahuja
Nov 19 '18 at 21:39

Because your code doesn't work as posted. If you fix the code so it works it would be considerably better.

– Sean Lange
Nov 19 '18 at 21:40

@Sean Lange Can you please check it now

– Himanshu Ahuja
Nov 19 '18 at 21:50

All you did was format it. There are still syntax errors. And since the OP posted such nice consumable data it really should work with what they posted.

– Sean Lange
Nov 20 '18 at 14:04

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu