SQL count query not returning correct results

Question

Struggling getting a query to work……..

I have two tables:-

tbl.candidates:

candidate_id agency_business_unit_id

tbl.candidate_employment_tracker

candidate_id

The candidate employment can have duplicate records of a candidate_id as it contains records on their working history for different clients.

The candidates tables is unique for each candidate.

I'm trying to obtain results which will group by agency_business_unit_id and count the amount of candidates each has which exist in the candidate_employment_tracker.

E.g.

Agency Business Unit Id    |    Candidates
------------------------------------------------------------
100                    |    2
987                    |    1
12                     |    90

The query I'm working on doesn't appear to be working as I'm getting the count of the candidates in candidate_employment_tracker.

SELECT 
    abu.agency_business_unit_id,
    abu.agency_business_unit_name,
    count(c.candidate_id) AS candidateCount
FROM candidate_employment_tracker cet
INNER JOIN candidate c ON c.candidate_id = cet.candidate_id
INNER JOIN agency_business_unit abu ON abu.agency_business_unit_id = c.agency_business_unit_id
WHERE c.candidate_ni_number NOT REGEXP '^[A-CEGHJ-PR-TW-Z][A-CEGHJ-NPR-TW-Z] ?[0-9]{2} ?[0-9]{2} ?[0-9]{2} ?[ABCD]$'
GROUP BY abu.agency_business_unit_id
ORDER BY abu.agency_business_unit_name ASC

I've tried several approaches and the results are inconsistent. For instance I know one of the agency business units only has 1 candidate but the result is 2. This is as a result of this particular candidate having 2 records in the candidate employment tracker table. I'll keep bashing away but any help would be much appreciated.

Answer 1

Do you need

count(DISTINCT c.candidate_id) 

That would avoid the double counting where candidates have 2 records in the candidate employment tracker table.

Answer 2

Hmmm this doesn't appear to work now that I look further into the results. When I compare the candidates for a agency business unit I get inconsistent count numbers.