select cmp.company_code, cmp.founder,

(SELECT COUNT(DISTINCT LM.lead_manager_code) FROM Lead_Manager LM WHERE LM.company_code = cmp.company_code) as lead,

(SELECT COUNT(DISTINCT SM.senior_manager_code) FROM Senior_Manager SM INNER JOIN Lead_Manager LM ON LM.lead_manager_code = SM.lead_manager_code WHERE LM.company_code = cmp.company_code) as senior_manager,

(SELECT COUNT(DISTINCT M.manager_code) FROM Manager M INNER JOIN Senior_Manager SM ON SM.senior_manager_code = M.senior_manager_code INNER JOIN Lead_Manager LM ON LM.lead_manager_code = SM.lead_manager_code WHERE LM.company_code = cmp.company_code) as manager,

(SELECT COUNT(DISTINCT E.employee_code) FROM Employee E INNER JOIN Manager M ON M.manager_code = E.manager_code INNER JOIN Senior_Manager SM ON SM.senior_manager_code = M.senior_manager_code INNER JOIN Lead_Manager LM ON LM.lead_manager_code = SM.lead_manager_code WHERE LM.company_code = cmp.company_code) as employee

FROM Company as cmp GROUP By cmp.company_code, cmp.founder

What is wrong with this answer?

SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code) AS lead_manager_count, COUNT(DISTINCT e.senior_manager_code) AS senior_manager_count, COUNT(DISTINCT e.manager_code) AS manager_count, COUNT(DISTINCT e.employee_code) AS employee_count FROM Employee e JOIN Company c ON e.company_code = c.company_code GROUP BY c.company_code, c.founder ORDER BY CAST(SUBSTRING(c.company_code, 2) AS UNSIGNED) ASC;

select c.company_code, c.founder,

count(distinct l.lead_manager_code),

count(distinct s.senior_manager_code),

count(distinct e.manager_code),

count(distinct e.employee_code) from Company as c

join lead_Manager as l on c.company_code = l.company_code
join senior_Manager as s on l.company_code = s.company_code join employee as e on s.company_code = e.company_code

GROUP BY c.company_code, c.founder;

SELECT DISTINCT 
    e.company_code, 
    Founder, COUNT(DISTINCT e.lead_manager_code), 
    COUNT(DISTINCT e.senior_manager_code), 
    COUNT(DISTINCT e.manager_code), 
    COUNT(DISTINCT e.employee_code) FROM Company c
JOIN Employee e ON e.company_code = c.company_code
GROUP BY e.company_code, Founder;

We dont need to join all the tables. Since all the data is present on employees table. we only need to join the company table

SELECT C.company_code, C.founder, COUNT(DISTINCT LM.lead_manager_code) AS Total Lead Manager, COUNT(DISTINCT SM.senior_manager_code) AS Total Senior Manager, COUNT(DISTINCT M.manager_code) AS Total Manager, COUNT(DISTINCT E.employee_code) AS Total Employee FROM Company C LEFT JOIN Lead_Manager LM ON LM.company_code = C.company_code LEFT JOIN Senior_Manager SM ON SM.company_code = C.company_code LEFT JOIN Manager M ON M.company_code = C.company_code LEFT JOIN Employee E ON E.company_code = C.company_code GROUP BY C.company_code, C.founder ORDER BY C.company_code ASC;