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, Nov 13 '17 at 07:22
Possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) — kvk30, Nov 13 '17 at 07:24

score 2 · Accepted Answer · answered Nov 13 '17 at 07:51

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 '17 at 07:51

Md. Suman Kabir

5,243
5
25
43

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!! – mrityunjay kumar Nov 13 '17 at 09:06
It's great that your problem is sorted out. Don't forget to mark it as useful (upvote) :) – Md. Suman Kabir Nov 13 '17 at 11:46

score 1 · Answer 2 · answered Nov 13 '17 at 07:22

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 '17 at 07:22

Jayasurya Satheesh

7,826
3
22
39

using count(distinct c.joined) is not helping, it is giving all the count as 1 @jayasurya – mrityunjay kumar Nov 13 '17 at 09:27
Might be because for each Country joined ID has only 1 unique value. In that cause , you may just remove the distinct from that – Jayasurya Satheesh Nov 13 '17 at 09:31

getting distinct count on join of two tables

2 Answers2