getting distinct count on join of two tables

Question

I have 2 tables where primary key of first table is used as foreign key for second table. In first table primary key is having unique values but in second table as foreign key it is having duplicate values as well. Now i want count from both the tables at same time and using left join for that. I have tried the following query :-

 select distinct count(c.joined) as joined,
    count(t.JobReqID),t.Country from txrecruitment as t 
    left join CandidateDetails as c on t.JobReqID=c.Jobreqid 
    where t.status='open'
    group by t.Country

The count value of count(t.JobReqID) is not giving count of distinct JobReqId which i want. The result of count is counting the multiple instances of 2nd table also which i want to remove. What should be the right way to do this. Any help is welcomed!!

Please share your expected result and actual result as text. — TheGameiswar
– TheGameiswar, Commented Nov 13, 2017 at 7:22
Possible duplicate of SQL Server query - Selecting COUNT(*) with DISTINCT — kvk30
– kvk30, Commented Nov 13, 2017 at 7:24

Md. Suman Kabir · Accepted Answer · 2017-11-13 07:51:08Z

2

Count using DISTINCT for the JobReqID like below :

select count(c.joined) as joined, count(distinct t.JobReqID), t.Country 
from txrecruitment as t 
left join CandidateDetails as c on t.JobReqID=c.Jobreqid 
where t.status='open'
group by t.Country

answered Nov 13, 2017 at 7:51

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrityunjay kumar Over a year ago

thanks both of you @kabir. It solved my problem but what can be the possible reason that count(c.joined) is giving count as one less than actual count. thanks again!!

Md. Suman Kabir Over a year ago

It's great that your problem is sorted out. Don't forget to mark it as useful (upvote) :)

Jayasurya Satheesh · Accepted Answer · 2017-11-13 07:22:58Z

1

Because our distinct is not placed in the correct location. Use Distinct Inside the Count.

 select count(distinct  c.joined) as joined,
    count(DISTINCT t.JobReqID),t.Country from txrecruitment as t 
    left join CandidateDetails as c on t.JobReqID=c.Jobreqid 
    where t.status='open'
    group by t.Country

answered Nov 13, 2017 at 7:22

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

2 Comments

mrityunjay kumar Over a year ago

using count(distinct c.joined) is not helping, it is giving all the count as 1 @jayasurya

Jayasurya Satheesh Over a year ago

Might be because for each Country joined ID has only 1 unique value. In that cause , you may just remove the distinct from that

Collectives™ on Stack Overflow

getting distinct count on join of two tables

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related