테이블
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 WORK_DATE
==== ======= ======== =====
1 1 1 1 1 1 1 1 04
10 10 10 10 10 10 10 10 03
1 1 1 1 1 1 1 1 03
4 4 4 4 4 4 54 4 04
1 1 1 1 1 1 1 1 03
55 50 55 0 160 0 0 0 03
1 1 1 2 5 0 0 0 03
1 1 1 1 4 1 1 1 03
1 1 1 1 4 4 4 4 02
1 2 3 55 61 3 3 3 02
3 3 3 3 2 2 2 2 05
10 1 1 1 2 1 10 1 03
정규직은 COL1 합
비정규직은 COL1~COL7의합
일반직은 COL8 의합을 달별기준으로 뿌려줄려고합니다 WORK_DATE 달별 기준으로
NAME CNT1,CNT2,CNT3,CNT4,CNT5,CNT6,CNT7~ CNT12
===== ==== ==== ==== ==== ==== ==== ~12
1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
정규직 1 2 3 4 5 6 7 8 9 10 11 12
비정규직 1 2 3 4 5 6 7 8 9 10 11 12
일반 1 2 3 4 5 6 7 8 9 10 11 12
총인원 3 6 9 12 15 18 21 24 ~
쿼리짜기 어려움있어서 도움을 요청드립니다. ㅠㅠ
예시가 빈약하네요. DBMS 종류도 없고.
work_date 가 두자리인게 이상하고,
일반직인지 정규직인지 구분 항목도 안보이네요.
-- Oracle -- WITH t AS ( SELECT 1 c1, 1 c2, 1 c3, 1 c4, 1 c5, 1 c6, 1 c7, 1 c8, '201904' dt, '일반' gb FROM dual UNION ALL SELECT 10, 10, 10, 10, 10, 10, 10, 10, '201903', '정규직' FROM dual UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, '201903', '정규직' FROM dual UNION ALL SELECT 4, 4, 4, 4, 4, 4, 54, 4, '201904', '정규직' FROM dual UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, '201903', '정규직' FROM dual UNION ALL SELECT 55, 50, 55, 0, 160, 0, 0, 0, '201903', '정규직' FROM dual UNION ALL SELECT 1, 1, 1, 2, 5, 0, 0, 0, '201903', '비정규직' FROM dual UNION ALL SELECT 1, 1, 1, 1, 4, 1, 1, 1, '201903', '비정규직' FROM dual UNION ALL SELECT 1, 1, 1, 1, 4, 4, 4, 4, '201902', '비정규직' FROM dual UNION ALL SELECT 1, 2, 3, 55, 61, 3, 3, 3, '201902', '비정규직' FROM dual UNION ALL SELECT 3, 3, 3, 3, 2, 2, 2, 2, '201905', '비정규직' FROM dual UNION ALL SELECT 10, 1, 1, 1, 2, 1, 10, 1, '201903', '비정규직' FROM dual ) SELECT gb , SUM(DECODE(mm, '01', c)) m01 , SUM(DECODE(mm, '02', c)) m02 , SUM(DECODE(mm, '03', c)) m03 , SUM(DECODE(mm, '04', c)) m04 , SUM(DECODE(mm, '05', c)) m05 , SUM(DECODE(mm, '06', c)) m06 , SUM(DECODE(mm, '07', c)) m07 , SUM(DECODE(mm, '08', c)) m08 , SUM(DECODE(mm, '09', c)) m09 , SUM(DECODE(mm, '10', c)) m10 , SUM(DECODE(mm, '11', c)) m11 , SUM(DECODE(mm, '12', c)) m12 FROM (SELECT gb , SUBSTR(dt, 5, 2) mm , DECODE(gb , '정규직' , c1 , '비정규직', c1+c2+c3+c4+c5+c6+c7 , '일반' , c8 ) c FROM t ) GROUP BY ROLLUP(gb) ;
답변감사드리고 죄송합니다 종류는 오라클입니다
제가한번 어제 쿼리 짜봣는데 문제될게 있을까요? 그룹바이를 2번감싸서 문제될꺼같은데
SELECT DIV cntDiv, SUM(cnt1) cnt1, SUM(cnt2) cnt2, SUM(cnt3) cnt3, SUM(cnt4) cnt4, SUM(cnt5) cnt5, SUM(cnt6) cnt6, SUM(cnt7) cnt7, SUM(cnt8) cnt8, SUM(cnt9) cnt9, SUM(cnt10) cnt10, SUM(cnt11) cnt11, SUM(cnt12) cnt12 FROM( SELECT DIV, DECODE(work_date,'01',cnt,0) cnt1, DECODE(work_date,'02',cnt,0) cnt2, DECODE(work_date,'03',cnt,0) cnt3, DECODE(work_date,'04',cnt,0) cnt4, DECODE(work_date,'05',cnt,0) cnt5, DECODE(work_date,'06',cnt,0) cnt6, DECODE(work_date,'07',cnt,0) cnt7, DECODE(work_date,'08',cnt,0) cnt8, DECODE(work_date,'09',cnt,0) cnt9, DECODE(work_date,'10',cnt,0) cnt10, DECODE(work_date,'11',cnt,0) cnt11, DECODE(work_date,'12',cnt,0) cnt12 FROM( SELECT DIV, WORK_DATE, SUM(cnt) cnt FROM( SELECT DIV, sum(cnt) cnt , WORK_DATE FROM ( SELECT '정규직' div, TO_NUMBER(NVL(col1,0)) cnt , TO_CHAR(WORK_DATE , 'MM') work_date FROM DUAL WHERE USE_YN ='Y' UNION ALL SELECT '비정규직' div, TO_NUMBER(NVL(col2+col3+col4+col5+col6+col7 ,0)) cnt , TO_CHAR(WORK_DATE , 'MM') work_date FROM DUAL WHERE USE_YN ='Y' UNION ALL SELECT '일반직' div, TO_NUMBER(NVL(col8,0)) , TO_CHAR(WORK_DATE , 'MM') work_date FROM DUAL WHERE USE_YN ='Y' ) GROUP BY DIV, WORK_DATE )A GROUP BY DIV, WORK_DATE )B )C GROUP BY ROLLUP(DIV) ORDER BY DIV
dual 로 표현한 부분을 동일 테이블이라고 간주하고 답변 드립니다.
WITH t AS ( SELECT 1 col1, 1 col2, 1 col3, 1 col4, 1 col5, 1 col6, 1 col7, 1 col8, TO_DATE('201904', 'yyyymm') work_date, 'Y' use_yn FROM dual UNION ALL SELECT 10, 10, 10, 10, 10, 10, 10, 10, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 4, 4, 4, 4, 4, 4, 54, 4, TO_DATE('201904', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 55, 50, 55, 0, 160, 0, 0, 0, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 1, 1, 1, 2, 5, 0, 0, 0, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 1, 1, 1, 1, 4, 1, 1, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 1, 1, 1, 1, 4, 4, 4, 4, TO_DATE('201902', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 1, 2, 3, 55, 61, 3, 3, 3, TO_DATE('201902', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 3, 3, 3, 3, 2, 2, 2, 2, TO_DATE('201905', 'yyyymm'), 'Y' FROM dual UNION ALL SELECT 10, 1, 1, 1, 2, 1, 10, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual ) SELECT DECODE(lv, 1, '정규직', 2, '비정규직', 3, '일반', '합계') div , NVL(SUM(DECODE(mm, '01', c)), 0) m01 , NVL(SUM(DECODE(mm, '02', c)), 0) m02 , NVL(SUM(DECODE(mm, '03', c)), 0) m03 , NVL(SUM(DECODE(mm, '04', c)), 0) m04 , NVL(SUM(DECODE(mm, '05', c)), 0) m05 , NVL(SUM(DECODE(mm, '06', c)), 0) m06 , NVL(SUM(DECODE(mm, '07', c)), 0) m07 , NVL(SUM(DECODE(mm, '08', c)), 0) m08 , NVL(SUM(DECODE(mm, '09', c)), 0) m09 , NVL(SUM(DECODE(mm, '10', c)), 0) m10 , NVL(SUM(DECODE(mm, '11', c)), 0) m11 , NVL(SUM(DECODE(mm, '12', c)), 0) m12 , SUM(c) tot FROM (SELECT TO_CHAR(work_date , 'mm') mm , lv , DECODE(lv, 1, col1 , 2, col2+col3+col4+col5+col6+col7 , 3, col8 ) c FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3) WHERE use_yn = 'Y' ) GROUP BY ROLLUP(lv) ;