SELECT USER
FROM DUAL;
--==>>SCOTT
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN TO_CHAR(DEPTNO)
ELSE '모든부서'
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
10 8750
20 10875
30 9400
(널) 8000
모든부서 37025*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '모든부서'
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
10 8750
20 10875
30 9400
인턴 8000
모든부서 37025*/
-- TBL_SAWON 테이블을 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
---------------------------------------------
성별 급여합
--------------------------------------------
남 XXXXX
여 XXXXXX
모든사원 XXXXXX
--------------------------------------------
*/
SELECT T.성별 "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1', '3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN ('2', '4') THEN '여'
ELSE '확인불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON
) T
GROUP BY ROLLUP(T.성별);
/*
남 12000
여 24100
널 36100*/
SELECT CASE GROUPING(T.성별) WHEN 0 THEN T.성별
ELSE '모든사원'
END "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1', '3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN ('2', '4') THEN '여'
ELSE '확인불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON
) T
GROUP BY ROLLUP(T.성별);
--==>>
/*
남 12000
여 24100
모든사원 36100*/
--● TBL_SAWON 테이블을 다음과 같이 연령대별 인원수 형태로
-- 조회할 수 있도록 쿼리문을 구성한다.
/*
---------------------------
연령대 인원수
---------------------------
10 X
20 X
30 X
40 X
50 X
전체 X
--------------------------
*/
-- [방법 1.] INLINE VIEW 를 한 번 중첩해서 보기
-- [방법 2.] INLINE VIEW를 한번만 사용해서 보기
-- [방법 1.] INLINE VIEW 를 한 번 중첩해서 보기
SELECT CASE WHEN T2.연령대 IS NULL THEN '전체'
ELSE TO_CHAR(T2.연령대)
END "연령대"
,COUNT(T2.연령대) "인원수"
FROM
(
SELECT CASE WHEN T.나이>=50 AND T.나이<60 THEN 50
WHEN T.나이>=40THEN 40
WHEN T.나이>=30THEN 30
WHEN T.나이>=20THEN 20
WHEN T.나이>=10THEN 10
ELSE 0
END "연령대"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (1900+TO_NUMBER(SUBSTR(JUBUN,1,2)))+1
WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (2000+TO_NUMBER(SUBSTR(JUBUN,1,2)))+1
ELSE 0
END "나이"
FROM TBL_SAWON
)T
)T2
GROUP BY ROLLUP(T2.연령대);
--==>>
/*
10 2
20 11
40 1
50 2
전체 16
*/
-- [방법 2.] INLINE VIEW를 한번만 사용해서 보기
SELECT TRUNC(28,-1) "확인"
FROM DUAL;
--===>>20
SELECT CASE WHEN T.연령대 IS NULL THEN '전체'
ELSE TO_CHAR(T.연령대)
END "연령대"
,COUNT(*) "인원수"
FROM
(
SELECT TRUNC(CASE WHEN SUBSTR(JUBUN,7,1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
WHEN SUBSTR(JUBUN,7,1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
ELSE 0
END,-1) "연령대"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.연령대);
--==>>
/*
10 2
20 11
40 1
50 2
전체 16
*/
--===========================================================================
--- ● ROLLUP 활용 및 CUBE
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1,2;
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600*/
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 (NULL) 8750 -- 10번 부서 모든 직종의 급여합
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 (NULL) 10875 -- 20 번 부서 모든 직종의 급여합
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 (NULL) 9400 -- 30번 부서 모든 직종의 급여합
(NULL) (NULL) 29025*/-- 모든 부서 모든 직종의 급여합
-- ● CUBE → ROLLUP() 보다 더 자세한 결과를 반환받을 수 있다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1,2;
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 (null) 8750 -- 10번 부서 모든 직종의 급여합
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 (null) 10875 -- 20번 부서 모든 직종의 급여합
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 (null) 9400 -- 30번 부서 모든 직종의 급여합
(null) ANALYST 6000 ▷ (부서관계없이) 모든부서 ANALYST 의 급여합
(null) CLERK 4150 ▷ (부서관계없이) 모든부서 CLERK 의 급여합
(null) MANAGER 8275 ▷ (부서관계없이) 모든부서 MANAGER 의 급여합
(null) PRESIDENT 5000Q ▷ (부서관계없이) 모든부서 PRESIDENT 의 급여합
(null) SALESMAN 5600 ▷ (부서관계없이) 모든부서 SALESMAN 의 급여합
(null) (null) 29025 -- 모든 부서 모든 직종의 급여합
*/
-- ※ ROLLUP() 과 CUBE() 는 그룹을 묶어주는 방식이 다르다(차이)
-- ROLLUP(A,B,C)
-- → (ABC)(AB)(A)()
-- CUBE(ABC)
-- → (ABC)(AB)(AC)(BC)(A)(B)(C)()
--==>> 위의 처리 내용은 원하는 결과를 얻지 못하거나
-- 너무 많은 결과물이 출력되기 때문에 다음의 쿼리 형태를 더 많이 사용
-- 다음 작성하는 쿼리는 조회하고자 하는 그룹만 『GROUPING SETS』를
-- 이용하여 묶어주는 방식이다.
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 500
인턴 전체직종 8000
전체부서 전체직종 37025
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1,2;
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 ANALYST 6000
전체부서 CLERK 6650
전체부서 MANAGER 8275
전체부서 PRESIDENT 5000
전체부서 SALESMAN 11100
전체부서 전체직종 37025
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY GROUPING SETS((DEPTNO, JOB),(DEPTNO),()) -- ROLLUP() 과 같은 결과
ORDER BY 1,2;
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 전체직종 37025*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY GROUPING SETS((DEPTNO, JOB),(DEPTNO),(JOB), ()) -- ROLLUP() 과 같은 결과
ORDER BY 1,2;
/*
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 1087
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 ANALYST 6000
전체부서 CLERK 6650
전체부서 MANAGER 8275
전체부서 PRESIDENT 5000
전체부서 SALESMAN 11100
전체부서 전체직종 37025*/
--연령대에서 SUBSTR 사용하면 안된다 7살 -> 70대가 되어버린다
-- ●TBL_EMP 테이블에서 입사년도별 인원수를 조회한다.
SELECT *
FROM TBL_EMP
ORDER BY HIREDATE;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--==>>Session이(가) 변경되었습니다.
/*
입사년도 인원수
------------------------
1980 1
1981 10
1982 1
1987 2
2021 5
전체 19
*/
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(EXTRACT(YEAR FROM HIREDATE)) "인원"
FROM TBL_EMP
GROUP BY GROUPING SETS(EXTRACT(YEAR FROM HIREDATE),())
ORDER BY 1;
/*
1980 1
1981 10
1982 1
1987 2
2021 5
(null) 19
*/
SELECT CASE WHEN EXTRACT(YEAR FROM HIREDATE) IS NULL THEN '전체'
ELSE TO_CHAR(EXTRACT(YEAR FROM HIREDATE))
END "입사년도"
, COUNT(EXTRACT(YEAR FROM HIREDATE)) "인원"
FROM TBL_EMP
GROUP BY GROUPING SETS(EXTRACT(YEAR FROM HIREDATE),())
ORDER BY 1;
/*
1980 1
1981 10
1982 1
1987 2
2021 5
전체 19
*/
--[선생님풀이]
SELECT CASE WHEN EXTRACT(YEAR FROM HIREDATE) IS NULL THEN '전체'
ELSE TO_CHAR(EXTRACT(YEAR FROM HIREDATE))
END "입사년도"
, COUNT(*) "인원"
FROM TBL_EMP
GROUP BY GROUPING SETS(EXTRACT(YEAR FROM HIREDATE),())
ORDER BY 1;
/*
1980 1
1981 10
1982 1
1987 2
2021 5
전체 19
*/
-- ★ 주의할점 : SELECT문에서는 입사년도를 EXTRACT() 를 통해 받고,
-- GROUP BY 에서는 TO_CHAR()로 입사년도를 받아 그룹을 구분하고자 할 때 에러가 발생한다.
-- 그 반대의 경우에도 마찬가지로 에러가 발생한다.
SELECT CASE WHEN EXTRACT(YEAR FROM HIREDATE) IS NULL THEN '전체'
ELSE TO_CHAR(EXTRACT(YEAR FROM HIREDATE))
END "입사년도"
, COUNT(*) "인원"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE, 'YYYY'))
ORDER BY 1;
/*
--===>> 에러발생 ROLLUP, CUBE , GROUPTING SETS 모두 에러난다.
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
535행, 36열에서 오류 발생*/
SELECT CASE WHEN TO_CHAR(HIREDATE, 'YYYY') IS NULL THEN '전체'
ELSE TO_CHAR(TO_CHAR(HIREDATE, 'YYYY'))
END "입사년도"
, COUNT(*) "인원"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE, 'YYYY'))
ORDER BY 1;
/*
1980 1
1981 10
1982 1
1987 2
2021 5
전체 19*/
SELECT CASE WHEN TO_CHAR(HIREDATE, 'YYYY') IS NULL THEN '전체'
ELSE TO_CHAR(TO_CHAR(HIREDATE, 'YYYY'))
END "입사년도"
, COUNT(*) "인원"
FROM TBL_EMP
GROUP BY ROLLUP(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--==>> "not a GROUP BY expression"