WITH t AS
(
SELECT '임원' dept_nm_kor, '홍길동' emp_nm_kor, '남' sex_or FROM dual
UNION ALL SELECT '임원', '임꺽정', '남' FROM dual
UNION ALL SELECT '관리팀', '안중근', '남' FROM dual
UNION ALL SELECT '관리팀', '윤봉길', '남' FROM dual
UNION ALL SELECT '관리팀', '이순신', '남' FROM dual
UNION ALL SELECT '출하팀', '유관순', '여' FROM dual
UNION ALL SELECT '출하팀', '신사임당', '여' FROM dual
)
SELECT DECODE(GROUPING_ID(dept_nm_kor, sex_or), 0, dept_nm_kor
                                              , 1, '부서계'
                                              , 2, sex_or||'자계'
                                              , 3, '전체계'
                                              ) dept_nm
     , NVL(emp_nm_kor, COUNT(*)) emp_nm
     , DECODE(GROUPING(emp_nm_kor), 0, sex_or) sex_or
  FROM t a
 GROUP BY GROUPING SETS ( (dept_nm_kor, emp_nm_kor, sex_or)
                        , (dept_nm_kor)
                        , (sex_or)
                        , (null)
                        )
 ORDER BY a.dept_nm_kor, a.emp_nm_kor
;