Query that provides the names of the recruiters that hire more than 3 employees, and the number of e-CodePudding

I'm a bit stuck on this question and was hoping for some help. Here's where I'm at currently.

I have this TEST table of Names. Each Person can either be a recruiter or an employee. The number in Recruited_by is associated with the person_id.

Person_id   Name    Recruited_by

1   Jean Grayson    1    
2   Paul Smith      7    
3   John Do         Null    
4   Alex Lee        7    
5   Lisa Kim        7    
6   Bob Thompson    3    
7   Mike Keen       Null    
8   Raymond Red     3    
9   Alisson Jones   1    
10  Kate James      3

Here is the query I have so far which I'm trying to the names of the recruiters that hire more than 3 employees (which will return nothing in this case) and the number of employees that were NOT recruited by anyone (which would be the NULL names).

SELECT T.Name as Employees, COUNT(T1.Name) as Not_hired
FROM Test AS T
WHERE COUNT(T1.Name) IS NULL
LEFT OUTER JOIN Test AS T1
ON T.Recruited_by = T1.Person_id
GROUP BY T.Name
HAVING COUNT(T1.Name) > 3

However this query is returning nothing when I should expect it to return the number of employees who were not hired by a recruiter!

CodePudding user response：

If you want in the results only 1 row with 2 columns then you can do a LEFT join of the table to a query that aggregates to get the ids of the persons that hired more than 3 persons and aggregate again to get the number of persons that were not recruited by anyone:

SELECT GROUP_CONCAT(CASE WHEN t2.Recruited_by IS NOT NULL THEN t1.Name END ORDER BY t1.Name) names,
       SUM(t1.Recruited_by IS NULL) total_not_recruited
FROM Test t1 
LEFT JOIN (
  SELECT Recruited_by
  FROM Test
  GROUP BY Recruited_by
  HAVING COUNT(*) > 3
) t2 ON t2.Recruited_by = t1.Person_id;

You will get the names of the persons that hired more than 3 persons (if they exist) as a comma separated list.

See the demo.