MySQL return total COUNT of each value in a column

I have a table of jobseekers with three columns

JOBSEEKER ID, EMPLOYER ID, HIRING STATUS

Each jobseeker could have different hiring status for each employer depending on the interview. Now I want to return the COUNT of each total HIRING STATUS But it should only count the jobseeker’s highest hiring status.

Say that John was ranked as QUALIFIED by employer 1 and HIRED by employer 2 John will only be counted under the highest hiring status he got which is HIRED by employer 2 and must not be counted under QUALIFIED.

HIRED: 1
QUALIFIED: 0
NEAR HIRED: 0
NOT QUALIFIED: 0

Here is my table

| Jobseeker Id | Employer Id | hstatus_id       |
|--------------|-------------|------------------|
|       1      | 2           | 1(Hired)         |
|       2      | 3           | 1(Hired)         |
|       2      | 4           | 3(Near Hire)     |
|       3      | 4           | 4(Not Qualified) |
|       1      | 2           | 2(Qualified)     |
|       3      | 3           | 1(Hired)         |
|       4      | 2           | 3(Near Hire)     |

and the result I want is

| Hiring Status | COUNT |
|---------------|-------|
|     Hired     | 3     |
|   Qualified   | 0     |
|   Near Hire   | 1     |
| Not Qualified | 0     |

Thank you, sorry for bad English.

Answer

You need a LEFT join of the table hStatus_table to a query that returns the minimum integer hiringstatus of each jobseekerid:

select s.hiringstatus, count(t.jobseekerid) counter
from hStatus_table s
left join (
  select jobseekerid, min(hstatus_id) hstatus_id
  from tablename
  group by jobseekerid
) t on t.hstatus_id = s.id
group by s.id, s.hiringstatus

I assume that the table hStatus_table is like this:

| ID  | HiringStatus  |
| --- | ------------- |
| 1   | Hired         |
| 2   | Qualified     |
| 3   | Near Hire     |
| 4   | Not Qualified |

See the demo.
Results:

| hiringstatus  | counter |
| ------------- | ------- |
| Hired         | 3       |
| Qualified     | 0       |
| Near Hire     | 1       |
| Not Qualified | 0       |