?HackerRank | New Companies

HackerRank New Companies

SELECT 절의 집계 함수 외의 필드는 반드시 GROUP BY 절에 포함되어야 한다.

SELECT
    C.company_code,
    C.founder,
    COUNT(DISTINCT LM.lead_manager_code) AS lead_manager_count,
    COUNT(DISTINCT SM.senior_manager_code) AS senior_manager_count,
    COUNT(DISTINCT M.manager_code) AS manager_count,
    COUNT(DISTINCT E.employee_code) AS employee_count

FROM
    Company AS C
LEFT JOIN
    Lead_Manager AS LM
ON
    C.company_code = LM.company_code
LEFT JOIN
    Senior_Manager AS SM
ON
    LM.lead_manager_code = SM.lead_manager_code
LEFT JOIN
    Manager AS M
ON
    SM.senior_manager_code = M.senior_manager_code
LEFT JOIN
    Employee AS E
ON
    M.manager_code = E.manager_code

GROUP BY
    C.company_code,
    C.founder
ORDER BY
    C.company_code ASC;