- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
ORACLE [Oracle|오라클] ROLLUP 합계, 소계 구하기 (GROUP BY)
2020.06.28 20:01
[Oracle|오라클] ROLLUP 합계, 소계 구하기 (GROUP BY)
오라클 GROUP BY 쿼리에서 ROLLUP 함수를 사용하여 손쉽게 합계와 소계를 구할 수 있다.
1. GROUP BY 컬럼이 하나인 경우
: GROUP BY 절에 ROLLUP 함수를 추가한 후 그룹핑 컴럼을 인자로 넣는다.
-- 가상 테이블
WITH TEST_TABLE AS (
SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
SELECT JOB
, SUM(SAL)
FROM TEST_TABLE
GROUP BY ROLLUP(JOB)
2. GROUP BY 컬럼이 두 개 이상인 경우
: 합계 및 소계 까지 계산되어 표시된다.
-- 가상 테이블
WITH TEST_TABLE AS (
SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
SELECT JOB
, DEPT
, SUM(SAL)
FROM TEST_TABLE
GROUP BY ROLLUP(JOB, DEPT)
3. GROUP BY 컬럼이 두 개 이상인 경우 합계만 표시하고 싶을 때
: HAVING 절에 GROUPING_ID 함수를 이용하여 소계 값을 제외할 수 있다.
-- 가상 테이블
WITH TEST_TABLE AS (
SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
SELECT JOB
, DEPT
, SUM(SAL)
FROM TEST_TABLE
GROUP BY ROLLUP(JOB, DEPT) HAVING GROUPING_ID(JOB, DEPT) IN (0, 3)
4. ROLLUP 컬럼에 합계 표시하기
: DECODE 함수를 이용하여 합계 컬럼 값을 NULL 대신 합계로 표시할 수 있다.
-- 가상 테이블
WITH TEST_TABLE AS (
SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
SELECT DECODE(JOB, NULL, '합계', JOB) JOB
, DEPT
, SUM(SAL)
FROM TEST_TABLE
GROUP BY ROLLUP(JOB, DEPT) HAVING GROUPING_ID(JOB, DEPT) IN (0, 3)
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 공지 | 오라클 기본 샘플 데이터베이스 | 졸리운_곰 | 2014.01.02 | 86303 |
| 공지 | [SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE | 가을의 곰을... | 2013.02.10 | 78755 |
| 공지 | [G_SQL] Sample Database | 가을의 곰을... | 2012.05.20 | 95502 |