WITH T AS
(
SELECT '서울' GB1, '서대문' GB2, '1' ID FROM DUAL UNION ALL
SELECT '서울' GB1, '서대문' GB2, '2' ID FROM DUAL UNION ALL
SELECT '서울' GB1, '서대문' GB2, '3' ID FROM DUAL UNION ALL
SELECT '서울' GB1, '종로' GB2, '1' ID FROM DUAL UNION ALL
SELECT '서울' GB1, '종로' GB2, '2' ID FROM DUAL UNION ALL
SELECT '부산' GB1, '북구' GB2, '5' ID FROM DUAL UNION ALL
SELECT '부산' GB1, '북구' GB2, '6' ID FROM DUAL UNION ALL
SELECT '부산' GB1, '동래구' GB2, '6' ID FROM DUAL UNION ALL
SELECT '서울' GB1, '동대문' GB2, '4' ID FROM DUAL
)
SELECT b.gb1
     , b.gb2
     , a.cnt1
     , b.cnt2
  FROM ( SELECT gb1, COUNT( DISTINCT id ) cnt1
           FROM t
          GROUP BY gb1 ) a
     , ( SELECT gb1, gb2, COUNT( DISTINCT id ) cnt2
           FROM t
          GROUP BY gb1, gb2 ) b
 WHERE a.gb1 = b.gb1
--일반 join문으로 하면 이렇게 하면 될 것 같은데 테이블에 두번 접근하게 되어 좀 비효율적이네요.
 
--rollup으로 한번만 접근하게 바꿔 봤습니다.
SELECT *
  FROM ( SELECT GB1
              , GB2
              , MAX( CNT2 ) OVER (PARTITION BY GB1) CNT1
              , CNT2
           FROM ( SELECT gb1, gb2, COUNT( DISTINCT id ) cnt2
                    FROM t
                  GROUP BY ROLLUP( gb1, gb2 ) 
                ) 
       )
 WHERE GB2 IS NOT NULL

그런데 예시 데이터 보면 광역시 회원수보다 대리점 회원수가 더 많을 수가 있나요?

SELECT gb1, gb2
     , cnt1
     , COUNT(*) cnt2
  FROM (SELECT gb1, gb2
             , COUNT(DISTINCT id) OVER(PARTITION BY gb1) cnt1
          FROM t
        )
 GROUP BY gb1, gb2, cnt1
;

 

SELECT DISTINCT gb1, gb2
     , COUNT(DISTINCT id) OVER(PARTITION BY gb1) cnt1
     , COUNT(*) OVER(PARTITION BY gb1, gb2) cnt2
  FROM t
 ORDER BY gb1, gb2
;

 

조언에 깊이 감사드립니다
각 광역시에 속한 대리점 회원은 광역시 회원하고 같습니다
대리점을 통한 회원가입입니다
그런데 집계는 공역시 입장에서는 아래 대리점들 전체 회원을 유니크하게
집계내고 각 대리점은 자기네 화원을 유니크하게 집계내기때문에 서로 다릅니다
감사합니다