MySQL count lines include zero (so close to what I want!)

Question

I know that I'm near, and he KILLED me. You need a fresh set of eyes ...

SELECT first_name, COUNT(*) FROM (SELECT first_name, job_status FROM typesetting LEFT JOIN employees ON typesetting.employees_id = employees.id LEFT JOIN job_status ON typesetting.job_status_id = job_status.id WHERE job_status NOT LIKE 'Archived' ORDER BY first_name ASC) AS CNT GROUP BY first_name 

This is me:

• Arnold (5)
• Claudia (19)
• Janet (29)
• Rich (21)
• Tom (4)
• Unrecognized (24)

However, there are some results with zero values ​​there that I desperately want in the results, for example:

• Arnold (5)
• Cheryl (0)
• Claudia (19)
• Cyndi (0)
• Janet (29)
• Phil (0)
• Rich (21)
• Tom (4)
• Unrecognized (24)

What am I doing wrong? So close! Thanks!

Matt

Answer 1

In the INNER query, you filter out all the people who can give you 0 results.

Try the following:

 SELECT first_name, sum(CASE WHEN job_status = 'Archived' THEN 1 ELSE 0 END) AS cnt FROM typesetting LEFT JOIN employees ON typesetting.employees_id = employees.id LEFT JOIN job_status ON typesetting.job_status_id = job_status.id GROUP BY first_name ORDER BY first_name ASC; 

Answer 2

 SELECT first_name, COUNT(js.id) FROM employees e LEFT JOIN typesetting ts ON ts.employees_id = e.id LEFT JOIN job_status js ON js.id = ts.job_status_id AND js.status <> 'Archived' GROUP BY first_name UNION ALL SELECT 'Unassigned', COUNT(*) FROM typesetting ts JOIN job_status js ON js.id = ts.job_status_id AND js.status <> 'Archived' WHERE ts.employees_id NOT IN ( SELECT id FROM employees ) 

Answer 3

If I receive your request correctly. You can do something like this:

 SELECT employees.first_name, ( SELECT COUNT(*) FROM typesetting JOIN job_status ON typesetting.job_status_id = job_status.id WHERE typesetting.employees_id = employees.id AND job_status NOT LIKE 'Archived' ) FROM employees ORDER BY first_name ASC