[ Oracle_SQL ] 집계(그룹) 함수 & GROUP BY 검색 & ROLLUP / CUBE

들어가기

서점에 운영자가 있다면 ,

운영자는 도서 판매 총액, 일별 판매량 등 도서 판매에 도움이 되는 집계된 정보가 필요하다.

집계하기 위해서는 GROUP BY 문을 사용하고 구체적인 집계 내용은 집계 함수를 사용한다.

🤸‍♀️ 집계는 통계와 비슷한 의미로, 데이터베이스에서는 통계보다 집계라는 용어를 사용한다.

학습목표

집계함수와 GROUP BY 검색에 대해 알아보자

---

📌 집계함수

여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수이다.

서점 운영자는 전체 도서의 판매액 합게를 알고 싶어 한다. 이를 위해서 집계 함수를 이용할 수 있다.

집계 함수는 테이블의 각 열에 대해 계산을 하는 함수로 SUM, AVG, MIN, MAX, COUNT의 5가지가 있다.

* 집계함수의 종류
- COUNT() : 검색된 행의 수를 반환한다.
- SUM() : 검색 된 행의 모든 합을 반환한다.
- MAX() : 검색된 행중에서 최대 값을 반환한다.
- MIN() : 검색 된 행중에서 최소 값을 반환한다.
- AVG() : 검색 된 행중에서 평균 값을 반환한다.

- 예제 -

--01_COUNT()
--컬럼의 값을 모아서 하나로 만드는 함수. 조회 결과가 1행이 도출
--레코드의 수를 셀 때 : count (컬럼명) -null인 컬럼은 집계 포함하지 않는다.

--예제
select * from EMP;

select count(empno),count(ename),count(hiredate),count(mgr),count(comm)
from emp;

--실습
--사원테이블에서 매니저가 없는 사원 수를 조회
select count(empno)-count(mgr),
'전체'||count(*)||'명 중 보너스를 수령하지 않는 인원은'||(count(ename)-count(comm))||'명 입니다.'msg
from emp;

예제 / 결과

실습 / 결과

--02
--sum(컬럼명)-컬럼의 값 합계,
--max(컬럼명)-컬럼 값 중 최고값
--min(컬럼명)-컬럼의 값 중 최소값
--avg(컬럼명)-컬럼 값의 평균

--02.1
--사원테이블에서 연봉의 합 조합
select sum(sal)
from emp;

--02.2
--사원테이블에서 연봉의 합,최고,최저,평균 연봉 조회
select sum(sal),max(sal),min(sal),avg(sal),trunc(avg(sal))
from emp;

--02.3
--집계함수는 where절에 사용할 수 없다.
--사원테이블에서 평균연봉 이상인 사원에 사원번호,사원명,연봉을 조회
select empno,ename,sal
from emp
where sal>=(select avg(sal)from emp);

--02.4
--여러 행이 조회되는 컬럼과 함께 사용되면 error
--sal -> 12개의 행이 조회, avg(sal)-1행 조회 => 조회되는 결과행이 맞지 않아서 error
select sal, avg(sal)
from emp;

02.1 / 결과

02.2 / 결과

02.3 / 결과

---

💗 GROUP BY 절

- 특정 범위별 집계 데이터를 얻기 위해서는 바로 '특정 범위'를 지정해 주어야 한다. 이를 "그룹을 짓는다" 라는 의미로 

  그룹핑 이라고 한다.

  이러한 특정 범위는 GROUP BY  절을 사용해서 지정한다.

  즉, GROUP BY절은 특정 컬럼을 기준으로 집계를 내는데 사용된다.

--문법)
--01
--전체 레코드의 집계 ( 1행만 나온다 )
select 집계함수( 컬럼명 ) 
from 테이블명;

--02
--그룹별 레코드의 집계 ( 그룹의 행만큼 조회 )
select 집계함수( 컬럼명 )
from 테이블명
group by 컬럼명;

- 예제 -

-- 집계함수는 group by와 함께 사용되면 그룹별 집계를 얻을 수 있다.
-- 사원테이블에서 부서별 인원수,부서별 연봉합,부서별 최고 연봉를 조회
select	deptno,count(*), sum( sal ), max( sal)
from		emp
group by deptno
order by deptno;

예제 / 결과

- 실습 -

-- 사원테이블에서 매니저별 관리사원수, 연봉합, 최고연봉,최저연봉, 평균연봉
-- 조회
-- 단,관리사원수가 2명이상 이면서 ,  매니저가 있는 사원만 조회   <- where  절

select	mgr, count( empno ), sum( sal ) , max(sal ),
			 min(sal) ,trunc(avg( sal))
from		emp
where 	mgr is not null
group by mgr
having count(empno) >= 2 ;

실습 / 결과

💌 ROLLUP / CUBE

- 그룹별 집계의 총합을 조회할 때 -

--문법)
group by rollup( 컬럼명 )

1. ROLLUP

group by rollup( 컬럼명 ) => 그룹별 합을 보여주고 총합을 마지막에 보여준다.

ROLLUP

2. CUBE

group by cube( 컬럼명 ) => 총합을 먼저 보여주고 그룹별 합을 보여준다.

CUBE

-- 그룹별 합을 조회
-- 사원테이블에서 부서별 연봉합과 총합을 조회

----컬럼하나가 그룹으로 묶인 경우-------------------------------------------------------------
--01
-- rollup : 그룹별 합을 먼저 보여주고, 총합을 나중에 보여준다.
select		deptno, sum( sal )
from			emp
group by	rollup(deptno);

--02
--cube : 총합을 먼저 보여주고, 그룹별합을 나중에 보여준다.
select		deptno, sum( sal )
from			emp
group by	cube(deptno);

01 / 결과

02 / 결과

- 컬럼 여러개가 그룹으로 묶인 경우 -

----컬럼여러개가 그룹으로 묶인 경우-------------------------------------------------------------
-- 사원 테이블에서 부서별 직무의 연봉합 조회

--01
-- 부서별합, 소계, 총계를 얻을 때 : rollup
select		deptno,job,sum(sal)
from			emp
group by	rollup(deptno,job)
order by deptno;

--02
-- 총계, 그룹별합, 소계,부서별 합을 얻을 때 : cube
select		deptno,job,sum(sal)
from			emp
group by	cube(deptno,job)
order by deptno;

01 / 총합, 그룹별 총합 조회

02 /  총합,그룹별 총합을 조회 후 그룹별 소계와 합을 조회

---

01. http://www.kyobobook.co.kr/< 도서 > 데이터베이스 개론과 실습

데이터베이스 개론과 실습 - 교보문고

02. https://lee1535.tistory.com/오라클 집계함수 설명

[오라클/Oracle] 집계함수 SUM(), MAX(), MIN(), AVG(), COUNT() 사용 방법

03. https://m.blog.naver.com/오라클 GRUOUP BY 절 설명

[Oracle]그룹 쿼리 - 집계함수, Group by

      https://ajdahrdl.tistory.com/

[Oracle] group by, having 사용 방법

      https://jink1982.tistory.com/

[Oracle] GROUP BY 절, HAVING 절