꿈꾸는 개발자, DBA 커뮤니티 구루비

중복제거 합과 전체합 함께 표시?

by 동동동 [SQL Query] [2018.10.25 17:31:46]

안녕하세요..일자별 중복제거된 COD의 Count와 전체 합을 같이 구하려고 하는데요..

WITH TMP AS (
SELECT '학교' DEP, 'AA' COD, '20181001' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'AA' COD, '20181001' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'AA' COD, '20181002' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'BB' COD, '20181002' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'BB' COD, '20181002' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'CC' COD, '20181002' DY FROM DUAL 
)
SELECT DEP
     , SUM(DECODE(SUBSTR(DY, 7,2), '01', 1)) DAYCNT01
     -- DAYCD01
     , SUM(DECODE(SUBSTR(DY, 7,2), '02', 1)) DAYCNT02
     -- DAYCD02
     , COUNT(DISTINCT COD) TOTCOD
     , SUM(1) TOTCNT    
FROM TMP
GROUP BY DEP

01일에는 AA가 두건이 있지만 중복제거 Count되어 1만 나와야 하고 02에는 BB의 중복제거되어 3으로 나오게 하고 싶습니다.

원하는 결과는

DEP	DAYCNT01	DAYCD01	DAYCNT02	DAYCD02	TOTCOD	TOTCNT
학교	2	1	4	3	3	6

하고 싶은데요..DAYCD01, DAYCD02를 어떻게 구해야 할지 모르겠습니다..

도움 부탁드립니다..

by 우리집아찌 [2018.10.25 17:43:28]

WITH TMP AS (
SELECT '학교' DEP, 'AA' COD, '20181001' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'AA' COD, '20181001' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'AA' COD, '20181002' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'BB' COD, '20181002' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'BB' COD, '20181002' DY FROM DUAL UNION ALL
SELECT '학교' DEP, 'CC' COD, '20181002' DY FROM DUAL 
)
SELECT DEP
     , SUM(DECODE(SUBSTR(DY, 7,2), '01', 1)) DAYCNT01
     , COUNT(DISTINCT DECODE(SUBSTR(DY, 7,2), '01', COD)) DAYCD01
     , SUM(DECODE(SUBSTR(DY, 7,2), '02', 1)) DAYCNT02
     , COUNT(DISTINCT DECODE(SUBSTR(DY, 7,2), '02', COD))  DAYCD02
     , COUNT(DISTINCT COD) TOTCOD
     , SUM(1) TOTCNT    
FROM TMP
GROUP BY DEP

by 동동동 [2018.10.25 18:54:36]

답변 감사드립니다..

한가지 더 여쭤봐도 될까요?

TOTCOD 의 값이 각 일자마다 중복된 COD를 제외한 합이 나오게 할 수 도 있을까요?

DAYCD01+DAYCD02 의 합을 일일이 기술하지 않고 구할수 있을련지요?

by 마농 [2018.10.25 18:46:21]

WITH tmp AS
(
SELECT '학교' dep, 'AA' cod, '20181001' dy FROM dual
UNION ALL SELECT '학교', 'AA', '20181001' FROM dual
UNION ALL SELECT '학교', 'AA', '20181002' FROM dual
UNION ALL SELECT '학교', 'BB', '20181002' FROM dual
UNION ALL SELECT '학교', 'BB', '20181002' FROM dual
UNION ALL SELECT '학교', 'CC', '20181002' FROM dual
)
SELECT dep
     , COUNT(DECODE(dd, '01', 1)) daycnt01, COUNT(DISTINCT DECODE(dd, '01', cod)) daycd01
     , COUNT(DECODE(dd, '02', 1)) daycnt02, COUNT(DISTINCT DECODE(dd, '02', cod)) daycd02
     , COUNT(DECODE(dd, '03', 1)) daycnt03, COUNT(DISTINCT DECODE(dd, '03', cod)) daycd03
     , COUNT(DECODE(dd, '04', 1)) daycnt04, COUNT(DISTINCT DECODE(dd, '04', cod)) daycd04
     , COUNT(DECODE(dd, '05', 1)) daycnt05, COUNT(DISTINCT DECODE(dd, '05', cod)) daycd05
     , COUNT(DECODE(dd, '06', 1)) daycnt06, COUNT(DISTINCT DECODE(dd, '06', cod)) daycd06
     , COUNT(DECODE(dd, '07', 1)) daycnt07, COUNT(DISTINCT DECODE(dd, '07', cod)) daycd07
     , COUNT(DECODE(dd, '08', 1)) daycnt08, COUNT(DISTINCT DECODE(dd, '08', cod)) daycd08
     , COUNT(DECODE(dd, '09', 1)) daycnt09, COUNT(DISTINCT DECODE(dd, '09', cod)) daycd09
     , COUNT(DECODE(dd, '10', 1)) daycnt10, COUNT(DISTINCT DECODE(dd, '10', cod)) daycd10
     , COUNT(DECODE(dd, '11', 1)) daycnt11, COUNT(DISTINCT DECODE(dd, '11', cod)) daycd11
     , COUNT(DECODE(dd, '12', 1)) daycnt12, COUNT(DISTINCT DECODE(dd, '12', cod)) daycd12
     , COUNT(DECODE(dd, '13', 1)) daycnt13, COUNT(DISTINCT DECODE(dd, '13', cod)) daycd13
     , COUNT(DECODE(dd, '14', 1)) daycnt14, COUNT(DISTINCT DECODE(dd, '14', cod)) daycd14
     , COUNT(DECODE(dd, '15', 1)) daycnt15, COUNT(DISTINCT DECODE(dd, '15', cod)) daycd15
     , COUNT(DECODE(dd, '16', 1)) daycnt16, COUNT(DISTINCT DECODE(dd, '16', cod)) daycd16
     , COUNT(DECODE(dd, '17', 1)) daycnt17, COUNT(DISTINCT DECODE(dd, '17', cod)) daycd17
     , COUNT(DECODE(dd, '18', 1)) daycnt18, COUNT(DISTINCT DECODE(dd, '18', cod)) daycd18
     , COUNT(DECODE(dd, '19', 1)) daycnt19, COUNT(DISTINCT DECODE(dd, '19', cod)) daycd19
     , COUNT(DECODE(dd, '20', 1)) daycnt20, COUNT(DISTINCT DECODE(dd, '20', cod)) daycd20
     , COUNT(DECODE(dd, '21', 1)) daycnt21, COUNT(DISTINCT DECODE(dd, '21', cod)) daycd21
     , COUNT(DECODE(dd, '22', 1)) daycnt22, COUNT(DISTINCT DECODE(dd, '22', cod)) daycd22
     , COUNT(DECODE(dd, '23', 1)) daycnt23, COUNT(DISTINCT DECODE(dd, '23', cod)) daycd23
     , COUNT(DECODE(dd, '24', 1)) daycnt24, COUNT(DISTINCT DECODE(dd, '24', cod)) daycd24
     , COUNT(DECODE(dd, '25', 1)) daycnt25, COUNT(DISTINCT DECODE(dd, '25', cod)) daycd25
     , COUNT(DECODE(dd, '26', 1)) daycnt26, COUNT(DISTINCT DECODE(dd, '26', cod)) daycd26
     , COUNT(DECODE(dd, '27', 1)) daycnt27, COUNT(DISTINCT DECODE(dd, '27', cod)) daycd27
     , COUNT(DECODE(dd, '28', 1)) daycnt28, COUNT(DISTINCT DECODE(dd, '28', cod)) daycd28
     , COUNT(DECODE(dd, '29', 1)) daycnt29, COUNT(DISTINCT DECODE(dd, '29', cod)) daycd29
     , COUNT(DECODE(dd, '30', 1)) daycnt30, COUNT(DISTINCT DECODE(dd, '30', cod)) daycd30
     , COUNT(DECODE(dd, '31', 1)) daycnt31, COUNT(DISTINCT DECODE(dd, '31', cod)) daycd31
     , COUNT(DISTINCT cod) totcod
     , COUNT(*) totcnt
  FROM (SELECT dep
             , cod
             , SUBSTR(dy, 7, 2) dd
          FROM tmp
         WHERE dy LIKE '201810%'
        )
 GROUP BY dep
;

by 동동동 [2018.10.25 18:56:15]

마농님 답변 감사드립니다...^^

by 마농 [2018.10.25 18:58:01]

SELECT *
  FROM (SELECT dep
             , NVL(TO_NUMBER(SUBSTR(dy, 7, 2)), 99) dd
             , COUNT(*) cnt
             , COUNT(DISTINCT cod) cod
          FROM tmp
         WHERE dy LIKE '201810%'
         GROUP BY dep, ROLLUP(SUBSTR(dy, 7, 2))
        )
 PIVOT (MIN(cnt) daycnt, MIN(cod) daycd
        FOR dd IN (  1,  2,  3,  4,  5,  6,  7,  8,  9, 10
                  , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
                  , 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
                  , 31, 99 AS tot)
        )
;

by 동동동 [2018.10.25 19:09:53]

마농님 답변 감사드립니다..

한가지 더 여쭤봐도 될까요? 위에서 추가 질문한 내용인데요..

TOTCOD 의 값이 각 일자마다 중복된 COD를 제외한 합이 나오게 할 수 도 있을까요?

DAYCD01+DAYCD02 의 합을 일일이 기술하지 않고 구할수 있을련지요?

by 마농 [2018.10.25 19:11:45]

WITH tmp AS
(
SELECT '학교' dep, 'AA' cod, '20181001' dy FROM dual 
UNION ALL SELECT '학교', 'AA', '20181001' FROM dual 
UNION ALL SELECT '학교', 'AA', '20181002' FROM dual 
UNION ALL SELECT '학교', 'BB', '20181002' FROM dual 
UNION ALL SELECT '학교', 'BB', '20181002' FROM dual 
UNION ALL SELECT '학교', 'CC', '20181002' FROM dual
)
SELECT dep
     , COUNT(DECODE(d, '01', 1)) daycnt01, COUNT(DECODE(d, '01', x)) daycod01
     , COUNT(DECODE(d, '02', 1)) daycnt02, COUNT(DECODE(d, '02', x)) daycod02
--     , COUNT(DECODE(d, '03', 1)) daycnt03, COUNT(DECODE(d, '03', x)) daycod03
--     , COUNT(DECODE(d, '04', 1)) daycnt04, COUNT(DECODE(d, '04', x)) daycod04
--     , COUNT(DECODE(d, '05', 1)) daycnt05, COUNT(DECODE(d, '05', x)) daycod05
--     , COUNT(DECODE(d, '06', 1)) daycnt06, COUNT(DECODE(d, '06', x)) daycod06
--     , COUNT(DECODE(d, '07', 1)) daycnt07, COUNT(DECODE(d, '07', x)) daycod07
--     , COUNT(DECODE(d, '08', 1)) daycnt08, COUNT(DECODE(d, '08', x)) daycod08
--     , COUNT(DECODE(d, '09', 1)) daycnt09, COUNT(DECODE(d, '09', x)) daycod09
--     , COUNT(DECODE(d, '10', 1)) daycnt10, COUNT(DECODE(d, '10', x)) daycod10
--     , COUNT(DECODE(d, '11', 1)) daycnt11, COUNT(DECODE(d, '11', x)) daycod11
--     , COUNT(DECODE(d, '12', 1)) daycnt12, COUNT(DECODE(d, '12', x)) daycod12
--     , COUNT(DECODE(d, '13', 1)) daycnt13, COUNT(DECODE(d, '13', x)) daycod13
--     , COUNT(DECODE(d, '14', 1)) daycnt14, COUNT(DECODE(d, '14', x)) daycod14
--     , COUNT(DECODE(d, '15', 1)) daycnt15, COUNT(DECODE(d, '15', x)) daycod15
--     , COUNT(DECODE(d, '16', 1)) daycnt16, COUNT(DECODE(d, '16', x)) daycod16
--     , COUNT(DECODE(d, '17', 1)) daycnt17, COUNT(DECODE(d, '17', x)) daycod17
--     , COUNT(DECODE(d, '18', 1)) daycnt18, COUNT(DECODE(d, '18', x)) daycod18
--     , COUNT(DECODE(d, '19', 1)) daycnt19, COUNT(DECODE(d, '19', x)) daycod19
--     , COUNT(DECODE(d, '20', 1)) daycnt20, COUNT(DECODE(d, '20', x)) daycod20
--     , COUNT(DECODE(d, '21', 1)) daycnt21, COUNT(DECODE(d, '21', x)) daycod21
--     , COUNT(DECODE(d, '22', 1)) daycnt22, COUNT(DECODE(d, '22', x)) daycod22
--     , COUNT(DECODE(d, '23', 1)) daycnt23, COUNT(DECODE(d, '23', x)) daycod23
--     , COUNT(DECODE(d, '24', 1)) daycnt24, COUNT(DECODE(d, '24', x)) daycod24
--     , COUNT(DECODE(d, '25', 1)) daycnt25, COUNT(DECODE(d, '25', x)) daycod25
--     , COUNT(DECODE(d, '26', 1)) daycnt26, COUNT(DECODE(d, '26', x)) daycod26
--     , COUNT(DECODE(d, '27', 1)) daycnt27, COUNT(DECODE(d, '27', x)) daycod27
--     , COUNT(DECODE(d, '28', 1)) daycnt28, COUNT(DECODE(d, '28', x)) daycod28
--     , COUNT(DECODE(d, '29', 1)) daycnt29, COUNT(DECODE(d, '29', x)) daycod29
--     , COUNT(DECODE(d, '30', 1)) daycnt30, COUNT(DECODE(d, '30', x)) daycod30
--     , COUNT(DECODE(d, '31', 1)) daycnt31, COUNT(DECODE(d, '31', x)) daycod31
     , COUNT(DISTINCT x) totcod
     , COUNT(x) totcod_1
     , COUNT(*) totcnt
  FROM (SELECT dep
             , TO_NUMBER(SUBSTR(dy, 7, 2)) d
             , DECODE(
               ROW_NUMBER() OVER(PARTITION BY dep, cod, SUBSTR(dy, 7, 2) ORDER BY 1)
               , 1, cod) x
          FROM tmp
         WHERE dy LIKE '201810%'
        )
 GROUP BY dep 
;

by 동동동 [2018.10.25 19:41:55]

마농님 답변 감사드립니다.

한번 감싸서 해야 하는 군요..

ROW_NUMBER() OVER(PARTITION BY dep, cod, SUBSTR(dy, 7, 2) ORDER BY 1) 에서

ORDER BY 1 => 이 1의 의미가 있는건가요??

================================================================

답변 정말 감사드립니다

by 마농 [2018.10.25 20:27:27]

중복되는 것끼리 번호를 부여하여 번호 1번만 표시한 것입니다.
건수를 세는게 중요하지 순서는 중요하지 않아 그냥 간단하게 1 적었습니다.
아무 의미 없는 그냥 숫자 1 입니다.
ORDER BY 절에 마땅히 적을 게 없어 적은 것입니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변