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=candidate s.candidat e_status
WHERE jobs.job_open=1 AND jobs.job_type=1
AND (candidates.candidate_stat us=75 OR candidates.candidate_statu s=14 OR candidates.candidate_statu s=45 OR candidates.candidate_statu s=19 OR candidates.candidate_statu s=41 OR candidates.candidate_statu s=35 OR candidates.candidate_statu s=39 OR candidates.candidate_statu s=40)
GROUP BY candidates.candidate_statu s;
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!
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
FROM candidates
INNER JOIN jobs ON jobs.job_id = candidates.candidate_jobid
INNER JOIN status ON status.status_id=candidate
WHERE jobs.job_open=1 AND jobs.job_type=1
AND (candidates.candidate_stat
GROUP BY candidates.candidate_statu
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER