select c.company_code, c.founder, count(distinct l.lead_manager_code), count(distinct s.senior_manager_code), count(distinct m.manager_code), count(distinct e.employee_code) from Company c

join Lead_Manager l on l.company_code = c.company_code join Senior_Manager s on s.company_code = c.company_code join Manager m on m.company_code = c.company_code join Employee e on e.company_code = c.company_code

group by c.company_code, c.founder order by c.company_code

MS SQL but so long. select C.Company_code ,C.founder ,L.C_L ,S.C_S ,M.C_M ,E.C_E from Company C left join ( select count(distinct L.lead_manager_code ) C_L ,L.Company_code from Lead_Manager L group by L.Company_code )L on L.Company_code = C.Company_code left join ( select count(distinct S.senior_manager_code) C_S ,S.Company_code from Senior_Manager S group by S.Company_code )S on S.Company_code = C.Company_code left join ( select count(distinct M.manager_code) C_M ,M.Company_code from Manager M group by M.Company_code )M on M.Company_code = C.Company_code left join ( select count(distinct E.Employee_code) C_E ,E.Company_code from Employee E group by E.Company_code )E on E.Company_code = C.Company_code order by C.Company_code asc

my working solution :)

SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code) AS total_lead_managers, COUNT(DISTINCT e.senior_manager_code) AS total_senior_managers, COUNT(DISTINCT e.manager_code) AS total_managers, COUNT(DISTINCT e.employee_code) AS total_employees FROM Company c LEFT JOIN Employee e ON c.company_code = e.company_code GROUP BY c.company_code, c.founder ORDER BY c.company_code ASC

CAN SOMEONE PLEASE EXPLAIN FOR ME WHY WE ALSO GROUP BY COMPANY.FOUNDER?

thank you!!

SELECT c.company_code, c.founder, COUNT(DISTINCT lm.lead_manager_code) AS total_lead_managers, COUNT(DISTINCT sm.senior_manager_code) AS total_senior_managers, COUNT(DISTINCT m.manager_code) AS total_managers, COUNT(DISTINCT e.employee_code) AS total_employees FROM Company c LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code LEFT JOIN Manager m ON c.company_code = m.company_code LEFT JOIN Employee e ON c.company_code = e.company_code GROUP BY c.company_code, c.founder ORDER BY c.company_code; -- Alphabetical order, not numeric