장비수 1개 아닌가영?

dept_idx = 6인 사람 3명에 그 3명이 가지고 있는 user_id는 1, 2, 3이고 장비에서 user_id 1하고 3은 없고 2만 있습니다 = 1개 

장비수 1개가 맞다면

WITH T_DEPT AS (
    SELECT 1 AS dept_idx, 0 AS dept_join, '야외 스포츠' AS dept_name FROM DUAL
    UNION ALL SELECT 2, 1, '축구' FROM DUAL
    UNION ALL SELECT 3, 1, '야구' FROM DUAL
    UNION ALL SELECT 4, 2, '아시안게임 축구 경기' FROM DUAL
    UNION ALL SELECT 5, 2, '월드컵 경기' FROM DUAL
    UNION ALL SELECT 6, 3, 'KBO' FROM DUAL
    UNION ALL SELECT 7, 3, 'MLB' FROM DUAL
    UNION ALL SELECT 8, 0, '실내스포츠' FROM DUAL
    UNION ALL SELECT 9, 8, '탁구' FROM DUAL
    UNION ALL SELECT 10, 8, '배드민턴' FROM DUAL
    UNION ALL SELECT 11, 9, '중국 탁구리그' FROM DUAL
    UNION ALL SELECT 12, 9, '한국 탁구리그' FROM DUAL
    UNION ALL SELECT 13, 9, '일본 탁구리그' FROM DUAL
), T_USER AS (
    SELECT 1 AS user_idx, '이승엽 (KBO)' AS user_name, 6 AS dept_idx FROM DUAL
    UNION ALL SELECT 2, '이병규 (KBO)', 6 FROM DUAL
    UNION ALL SELECT 3, '선동렬 (KBO)', 6 FROM DUAL
    UNION ALL SELECT 4, 'Scott Alexander (MLB)', 7 FROM DUAL
    UNION ALL SELECT 5, '이용대 (배드민턴)', 10 FROM DUAL
    UNION ALL SELECT 6, '이동수 (배드민턴)', 10 FROM DUAL
), T_EQUIPMENT AS (
    SELECT 1 AS equipment_idx, '엘지트윈스 배트' AS equipment_name, 2 AS user_idx FROM DUAL
    UNION ALL SELECT 2, '선동렬 글러브', 6 FROM DUAL
    UNION ALL SELECT 3, '갤럭시 S9', 10 FROM DUAL
    UNION ALL SELECT 4, '고급 배드민턴 공', 10 FROM DUAL
)
SELECT
    COUNT(*) AS 유저수, MAX((SELECT COUNT(*) FROM T_EQUIPMENT WHERE user_idx = T_USER.user_idx)) AS 장비수
FROM
    T_USER
WHERE
    dept_idx = 6
;

장비테이블의 키가 어떻게 되나요?
 - 장비ID 만 키인가요?  ==> 모든 장비는 한사람만 관리
 - 장비ID + 유저ID 가 키인가요? ==> 같은 장비를 여러명이 쓸 수 있음.

WITH t_dept AS
(
SELECT 1 dept_idx, 0 dept_join, '야외 스포츠' dept_name FROM dual
UNION ALL SELECT  2, 1, '축구'                 FROM dual
UNION ALL SELECT  3, 1, '야구'                 FROM dual
UNION ALL SELECT  4, 2, '아시안게임 축구 경기' FROM dual
UNION ALL SELECT  5, 2, '월드컵 경기'          FROM dual
UNION ALL SELECT  6, 3, 'KBO'                  FROM dual
UNION ALL SELECT  7, 3, 'MLB'                  FROM dual
UNION ALL SELECT  8, 0, '실내스포츠'           FROM dual
UNION ALL SELECT  9, 8, '탁구'                 FROM dual
UNION ALL SELECT 10, 8, '배드민턴'             FROM dual
UNION ALL SELECT 11, 9, '중국 탁구리그'        FROM dual
UNION ALL SELECT 12, 9, '한국 탁구리그'        FROM dual
UNION ALL SELECT 13, 9, '일본 탁구리그'        FROM dual
)
, t_user AS
(
SELECT 1 user_idx, '이승엽 (KBO)'  user_name, 6 dept_idx FROM dual
UNION ALL SELECT 2, '이병규 (KBO)'         ,  6 FROM dual
UNION ALL SELECT 3, '선동렬 (KBO)'         ,  6 FROM dual
UNION ALL SELECT 4, 'Scott Alexander (MLB)',  7 FROM dual
UNION ALL SELECT 5, '이용대 (배드민턴)'    , 10 FROM dual
UNION ALL SELECT 6, '이동수 (배드민턴)'    , 10 FROM dual
)
, t_equipment AS
(
SELECT 1 equipment_idx, '엘지트윈스 배트' equipment_name, 2 user_idx FROM dual
UNION ALL SELECT 2, '선동렬 글러브'   ,  6 FROM dual
UNION ALL SELECT 3, '갤럭시 S9'       , 10 FROM dual
UNION ALL SELECT 4, '고급 배드민턴 공', 10 FROM dual
)
SELECT COUNT(DISTINCT u.user_idx) user_cnt
     , COUNT(e.equipment_idx) equipment_cnt
--     , COUNT(DISTINCT e.equipment_idx) equipment_cnt
  FROM (SELECT *
          FROM t_dept
         START WITH dept_idx = 6
         CONNECT BY PRIOR dept_idx = dept_join
        ) d
     , t_user u
     , t_equipment e
 WHERE d.dept_idx = u.dept_idx(+)
   AND u.user_idx = e.user_idx(+)
;

답장주신분들 감사합니다. 답장이 늦어죄송합니다. 장비테이블 두번째 로우 오타입니다유저idx 3으로 정정합니다. 그리고 말씀해주신대로 쿼리를 작성하면 데이터가 추가될때마다 쿼리를 수정해야되는것 아닌가요?? Deptidx만으로 조회하는건 쿼리로 불가능할까요?

넵 마농님 이해했습니다.

다만 제가 사용하는 DB를 적지않았었네요.. MYSQL사용중이라 START WITH CONNECT BY PRIOR를 사용하지못합니다.

대안을 생각해서 적용시켜보겠습니다.

답변주신 아발란체님 마농님 감사드립니다.

5.7버전입니다!

마농님 답변주신 내용 토대로 아래와 같이 고쳐서 완성했습니다. 도움주셔서 감사합니다!


SELECT 
       COUNT(DISTINCT u.user_idx) user_cnt
     , COUNT(i.equipment_idx) equipment_cnt
	 
  FROM (
		select  DEPT_IDX,
                            DEPT_NAME,
                           DEPT_JOIN
from    (select * from T_DEPT
          order by DEPT_JOIN, DEPT_IDX) products_sorted,
          (select @pv := '6') initialisation
where   (find_in_set(DEPT_JOIN, @pv)
          and  length(@pv := concat(@pv, ',', DEPT_IDX))) or dept_idx = 6		
		) d 
left join t_user u
on(d.dept_idx = u.DEPT_IDX)
left join T_EQUIPMENT i
on(u.user_idx = i.USER_IDX)