Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Finding a name based on another column count

I have this table employee3_3:

id name department managerId
101 John A null
102 Dan A 101
103 James A 101
104 Amy A 101
105 Anne A 101
106 Ron A 101

Here John is the manager, that’s why he doesn’t have a managerId value. But to prove he is the manager, I’m asked to write a query to find managers with at least five direct employees.

I tried this:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

WITH cte AS
( 
    SELECT 
        name, managerid,
        (SELECT COUNT(b.managerid) 
         FROM employee3_3 AS b) AS result
    FROM
        employee3_3
)
SELECT employee3_3.name
FROM employee3_3
FULL JOIN cte ON employee3_3.name = cte.name
WHERE result = 5    
  AND cte.managerid = NULL

For some reason, it doesn’t return any result.

I also tried this:

WITH cte AS
(
    SELECT  
        managerid, COUNT(managerid) AS number_of_employees
    FROM 
        employee3_3
    GROUP BY
        managerid, managerid
)
SELECT name
FROM employee3_3 AS e
JOIN cte ON e.managerid = cte.managerid
WHERE cte.number_of_employees <= 5

I’m not sure if is my approach that is wrong and I’m not in the right path, or if I’m not using the right functions.

>Solution :

select e.* 
from employee3_3 e
inner join
(select managerId
from employee3_3
group by managerId having count(*) >= 5 
) m on e.Id = m.managerId;

DBFiddle demo

PS: If a manager is meant to be her\his managerId is null then you should add that criteria (then only top level managers would count as manager)

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading