mysql 5.7 version 에서는 계층쿼리를 지원하지 않는 것으로 알고 있습니다.
테이블 구조가 코드와  상위코드만 가지고 있다면 접근하기 쉽지 않습니다.
테이블 자체에 직상위 뿐만아니라 전체 계층 상위코드들을 다 가지고 있다던가 하면 좋을 것 같습니다.

WITH dept AS
(
SELECT 1000 deptno, 'A' dname, null p_deptno
UNION ALL SELECT 1100, 'B', 1000
UNION ALL SELECT 1110, 'C', 1100
UNION ALL SELECT 1111, 'D', 1110
UNION ALL SELECT 1112, 'E', 1110
UNION ALL SELECT 1200, 'F', 1000
UNION ALL SELECT 1210, 'G', 1200
UNION ALL SELECT 1211, 'H', 1210
UNION ALL SELECT 1212, 'I', 1210
)
, emp AS
(
SELECT 1 empno, 'a' ename, 1000 deptno
UNION ALL SELECT  2, 'b', 1100
UNION ALL SELECT  3, 'c', 1200
UNION ALL SELECT  4, 'd', 1110
UNION ALL SELECT  5, 'e', 1110
UNION ALL SELECT  6, 'f', 1111
UNION ALL SELECT  7, 'g', 1111
UNION ALL SELECT  8, 'h', 1111
UNION ALL SELECT  9, 'i', 1112
UNION ALL SELECT 10, 'j', 1112
UNION ALL SELECT 11, 'k', 1112
UNION ALL SELECT 12, 'l', 1112
UNION ALL SELECT 13, 'm', 1112
UNION ALL SELECT 14, 'n', 1210
UNION ALL SELECT 15, 'o', 1210
UNION ALL SELECT 16, 'p', 1211
UNION ALL SELECT 17, 'q', 1211
)
SELECT d.deptno
     , d.dname
     , COUNT(CASE WHEN d.deptno = e.deptno THEN 1 END) cnt
     , COUNT(e.deptno) cnt_sum
  FROM dept d  -- 조직
 INNER JOIN
       (-- 상위 조직 찾기 : 최대 가능 레벨만큼 조인 추가
        SELECT a.deptno a
             , b.deptno b
             , c.deptno c
             , d.deptno d
--           , e.deptno e
          FROM dept a
          LEFT OUTER JOIN dept b ON a.p_deptno = b.deptno
          LEFT OUTER JOIN dept c ON b.p_deptno = c.deptno
          LEFT OUTER JOIN dept d ON c.p_deptno = d.deptno
--        LEFT OUTER JOIN dept e ON d.p_deptno = e.deptno
        ) x
    ON d.deptno IN ( a, b, c, d
--                 , e
                   )
  LEFT OUTER JOIN emp e  -- 사원
    ON x.a = e.deptno
 GROUP BY d.deptno, d.dname
;