Link to home
Start Free TrialLog in
Avatar of Alex Pakula
Alex Pakula

asked on

SQL question - need unique values for one column that is not displayed

Hello!

I'm still learning SQL and having some trouble with a complex query.  I'm using MySQL. Here's the query so far:

SELECT status.status_name, COUNT(candidates.candidate_status) AS "Count"
FROM candidates
INNER JOIN jobs ON jobs.job_id = candidates.candidate_jobid
INNER JOIN status ON status.status_id=candidates.candidate_status
WHERE jobs.job_open=1 AND jobs.job_type=1
AND (candidates.candidate_status=75 OR candidates.candidate_status=14 OR candidates.candidate_status=45 OR candidates.candidate_status=19 OR candidates.candidate_status=41 OR candidates.candidate_status=35 OR candidates.candidate_status=39 OR candidates.candidate_status=40)
GROUP BY candidates.candidate_status;

My issue is that there are sometimes multiple instances of candidate_status on one job_id, and I need to count each of those only once.

How do I get distinct job_id values when I don't want to display the job_id?

The results should look like this:

Interviewing      44
Submitted      211
Interviewing Site Visit      11
Closing      9
Pending Search      11

but the counts should be smaller.

Thank you so much!
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alex Pakula
Alex Pakula

ASKER

Thank you so much!!!  I'm still trying to get the logic of all of this down.  I appreciate the swiftness of your response and this worked perfectly!