오라클 소계값, 총계값, ROLLUP

 

월급과 보너스를 합하기

원본

SELECT *
FROM EMP;

 

 

SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM)
FROM EMP;

 

 

소계값, 총계값을 UNION ALL 로 만들기

SELECT ENAME, JOB, DEPTNO, TO_CHAR(S,'$999,999')
FROM
(
SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM) "S", DEPTNO
FROM EMP
ORDER BY DEPTNO
)
WHERE DEPTNO = 10

UNION ALL

-- 유니온 올 하려면 타입을 같게 맟춰야한다 TO_CHAR까지...
SELECT '소계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP
WHERE DEPTNO = 10

UNION ALL

SELECT ENAME, JOB, DEPTNO, TO_CHAR(S,'$999,999')
FROM
(
SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM) "S", DEPTNO
FROM EMP
ORDER BY DEPTNO
)
WHERE DEPTNO = 20

UNION ALL

SELECT '소계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP
WHERE DEPTNO = 20

UNION ALL

SELECT ENAME, JOB, DEPTNO, TO_CHAR(S,'$999,999')
FROM
(
SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM) "S", DEPTNO
FROM EMP
ORDER BY DEPTNO
)
WHERE DEPTNO = 30

UNION ALL

SELECT '소계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP
WHERE DEPTNO = 30

UNION ALL

SELECT '총계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP;

 

 

SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL),1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY DEPTNO

UNION ALL

SELECT DEPTNO, JOB, ROUND(AVG(SAL),1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY DEPTNO, JOB

UNION ALL

SELECT NULL DEPTNO, NULL JOB, ROUND(AVG(SAL),1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
ORDER BY DEPTNO, JOB;

 

소계값, 총계값을 ROLLUP으로 구하기

SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY ROLLUP (DEPTNO, JOB);

 

 

--학생 테이블의 키의 평균을 구하시오.
SELECT AVG(HEIGHT)
FROM STUDENT;

 

 

--학생 테이블에서 DEPTNO1 별 학생들의 키의 평균을 구하시오. 
SELECT DEPTNO1, AVG(HEIGHT)
FROM STUDENT
GROUP BY DEPTNO1;

 

 

--학생 테이블에서 DEPTNO1별 학생들의  키의 평균과 소계값을 구하시오.
SELECT DEPTNO1, GRADE, ROUND(AVG(HEIGHT), 1) , COUNT(*)
FROM STUDENT
GROUP BY ROLLUP (DEPTNO1, GRADE);

 

 

-- 직업별 급여합계 조회 마지막줄에 총합계 추가해서 표시
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP (JOB);

 

 

--PANMAE 테이블에서 날짜별(P_DATE) 상품이(P_CODE) 몇개(P_QTY) 판매 되었는지와 소계값을 구하시오
SELECT P_DATE, P_CODE, SUM(P_QTY)
FROM PANMAE
GROUP BY ROLLUP (P_DATE, P_CODE);

 

 

--PANMAE 테이블에서 상품별(P_CODE)로 날짜마다(P_DATE) 얼마의 금액(P_TOTAL)이 판매 되었는지와 소계값을 구하시오
SELECT P_CODE, P_DATE, SUM(P_TOTAL)
FROM PANMAE
GROUP BY ROLLUP (P_CODE, P_DATE);