메뉴 건너뛰기

프로그램언어

Home 프로그램언어 DB 오라클
✔ 뷰어로 보기
2016.12.08 02:37

ROLLUP 합계, 소계 구하기 (GROUP BY)

조쉬
조회 수 11869 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

가 크게 작게 위로 아래로 댓글로 가기 인쇄 첨부
?

단축키

Prev이전 문서

Next다음 문서

가 크게 작게 위로 아래로 댓글로 가기 인쇄 첨부

오라클 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)

 

 

Facebook Twitter Google Pinterest
위로 아래로 댓글로 가기 인쇄 첨부
에디터 선택하기
?
  1. No Image 27Oct
    by
    2018/10/27 Views 4284 

    오라클 10, 16진수(Decimal, Hex) 변환

  2. No Image 27Oct
    by
    2018/10/27 Views 2149 

    오라클 랜덤함수

  3. No Image 20Jan
    by
    2017/01/20 Views 8329 

    오늘을 기준으로 해당주(week)의 모든 일자조회

  4. 20Jan
    by
    2017/01/20 Views 8620 

    오라클] 숫자 체크 방법 (IS_NUMBER, IS_NUMERIC)

  5. No Image 20Jan
    by
    2017/01/20 Views 8241 

    오라클 ORDER BY : 정렬

  6. No Image 20Jan
    by
    2017/01/20 Views 7201 

    오라클 NULL : 값이 존재하지 않는 상태

  7. 08Dec
    by
    2016/12/08 Views 10855 

    CentOS 6.5 에 Oracle Database 11g Release 2 설치하기

  8. No Image 08Dec
    by
    2016/12/08 Views 8582 

    RMAN 을 이용한 오라클 백업

  9. No Image 08Dec
    by
    2016/12/08 Views 10385 

    [Oracle] Rownum을 이용한 페이징 처리

  10. No Image 08Dec
    by
    2016/12/08 Views 8572 

    [Oracle] 테이블 스페이스 및 사용자 계정 생성 방법

  11. No Image 08Dec
    by
    2016/12/08 Views 12607 

    [Oracle] DB Export, Import 방법

  12. 08Dec
    by
    2016/12/08 Views 9756 

    오라클 백업 및 복구(Export, Import)

  13. No Image 08Dec
    by
    2016/12/08 Views 9926 

    오라클 DB 백업과 복원

  14. 08Dec
    by
    2016/12/08 Views 9877 

    데이터베이스 백업하기 ( import : 가져오기 , export : 내보내기 )

  15. 08Dec
    by
    2016/12/08 Views 14092 

    컬럼의 값 만큼 행(Row)을 늘리기

  16. 08Dec
    by 조쉬
    2016/12/08 Views 11869 

    ROLLUP 합계, 소계 구하기 (GROUP BY)

  17. 08Dec
    by
    2016/12/08 Views 12822 

    여러개(다중) LIKE 검색 방법 (REGEXP_LIKE 함수)

  18. 08Dec
    by
    2016/12/08 Views 15026 

    숫자를 문자로 변환 시 소수점 처리 (TO_CHAR, FM)

  19. 08Dec
    by
    2016/12/08 Views 9383 

    PL/SQL에서 자바(Java) 클래스(Class), 함수 실행 방법

  20. 08Dec
    by
    2016/12/08 Views 23119 

    숫자 체크 방법 (IS_NUMBER, IS_NUMERIC)

쓰기
Board Pagination Prev 1 2 3 4 5 6 7 Next
/ 7

하단 정보를 입력할 수 있습니다

© 2016 JOSHI. All Rights Reserved
© k2s0o1d4e0s2i1g5n. All Rights Reserved