6장 그룹 함수.

Presentation on theme: "6장 그룹 함수."— Presentation transcript:

1 6장 그룹 함수

2 목 차 그룹 함수 로우(행)의 개수를 세는 COUNT 함수 합을 구하는 SUM 함수 평균을 구하는 AVG 함수
MAX 함수와 MIN 함수 HAVING 절 GROUP BY 절

3 그룹 함수 그룹함수는 단 하나의 값만 출력

4 그룹 함수 SELECT [컬럼], Group_Function(컬럼), … FROM 테이블, 테이블, .. [WHERE 조건]
[GROUP BY 컬럼] [ORDER BY 컬럼];

5 로우(행)의 개수를 세는 COUNT 함수 직원 총 수와 커미션을 받는 사원의 수 조회
SQL> SELECT count(*), COUNT(comm)    2  FROM  emp; 10번 부서의 커미션을 받는 사원의 수 조회 SQL> SELECT COUNT(comm)    2  FROM   emp    3  WHERE deptno=10;

6 로우(행)의 개수를 세는 COUNT 함수 –cont’d
직책의 종류 조회 SQL> SELECT COUNT(job)    2  FROM  emp; SQL> SELECT COUNT(distinct job) Count(job)은 직책의 갯수임 영업사원들의 급여 총액과 평균 조회 SQL> SELECT SUM(sal), AVG(sal) 2 FROM emp 3 WHERE job = ‘SALESMAN’;

7 합을 구하는 SUM 함수 사원의 급여 총합 조회 SQL> SELECT SUM(sal) 2 FROM emp;
전체 사원 수와 입사한 해의 사원 수 조회 SQL> SELECT COUNT(*) “total”, 2 SUM(DECODE(TO_CHAR(hiredate, ‘YYYY’),1980,1,0)) “1980”, 3 SUM(DECODE(TO_CHAR(hiredate, ‘YYYY’),1981,1,0)) “1981”, 4 SUM(DECODE(TO_CHAR(hiredate, ‘YYYY’),1982,1,0)) “1982”, 5 SUM(DECODE(TO_CHAR(hiredate, ‘YYYY’),1987,1,0)) “1987”    6  FROM   emp    입사일이 1980이면 1을, 아니면 0을 sum()함수에 적용

8 평균을 구하는 AVG 함수 사원 급여의 평균 조회 SQL> SELECT AVG(sal) 2 FROM emp;
사원 총수와 커미션의 평균 조회 SQL> SELECT  count(*) 사원수, AVG(comm)   2   FROM emp; Avg(comm)은 커미션받는 사원에 대한 평균임-모순 사원총수와 커미션의 합, 커미션받는 사원수, 커미션의 전사원 평균 조회 SQL> SELECT  count(*), sum(comm), count(comm), AVG(NVL(comm, 0))   2   FROM emp;

9 그룹 함수를 이용한 데이터 집계 그룹 함수와 NULL 값 사원수, 수당합계, 수당이 있는 사원 수,수당평균의 조회
SQL> SELECT COUNT(*) 사원수, SUM(comm) "수당 합계", COUNT(comm) "수당 사원수", AVG(NVL(comm, 0)) "수당 평균" 4 FROM emp;

10 최대값(MAX) 함수, 최소값(MIN) 함수
사원 급여중 최대값과 최소값 조회 SQL> SELECT  MAX(sal), MIN(sal)   2   FROM emp; 사원이름, 입사일의 최대값과 최소값 조회 SQL> SELECT  MAX(ename), MIN(ename), MAX(hiredate),MIN(hiredate)   2   FROM emp;

11 그룹 함수를 이용한 데이터 집계 GROUP BY 절을 사용한 행 그룹 짓기 GROUP BY 절 사용시 가장 중요한 규칙
전체 사원의 급여 합 조회. SQL> SELECT SUM(sal) 2 FROM emp; 부서별 급여 합 조회. SQL> SELECT deptno, SUM(sal) 에러: ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

12 그룹 함수를 이용한 데이터 집계 GROUP BY 절을 사용한 행 그룹 짓기 GROUP BY 절 사용시 가장 중요한 규칙

13 GROUP BY 절 기본 형식 예 제 부서별 평균 급여 조회 SQL> SELECT deptno, AVG(sal)
FROM   테이블명 WHERE  조건 (연산자) GROUP BY 컬럼명 예 제 부서별 평균 급여 조회 SQL> SELECT deptno, AVG(sal)   2   FROM emp   3   GROUP BY deptno;

14 GROUP BY 절 부서별 커미션을 받는 사원들의 수를 계산
부서별 커미션을 받는 사원들의 수를 계산 SQL> SELECT deptno, COUNT(*), COUNT(comm)   2   FROM emp   3   GROUP BY deptno; 부서별 최대 급여와 최소 급여를 산출 SQL> SELECT deptno, MAX(sal), MIN(sal)   2   FROM emp   3   GROUP BY deptno;

15 GROUP BY 절 부서별, 직책별 사원 수 조회 SQL> SELECT deptno, job, COUNT(*) 사원수
  2   FROM emp   3   GROUP BY deptno, job; 부서별 평균급여의 최대값 조회 SQL> SELECT MAX(AVG(sal))   2   FROM emp   3   GROUP BY deptno; Select 절에 ~별에 해당하는 컬럼 생략 가능 그룹함수 복합 적용 가능 15

16 GROUP BY 절 다중 컬럼 GROUP BY

17 HAVING 절 부서별 평균 급여가 2000 이상인 부서번호와 평균급여 산출
HAVING절은 GROUP BY절에 의해 생성된 그룹이 특정 조건에 맞을떄 사용 부서별 평균 급여가 2000 이상인 부서번호와 평균급여 산출 SQL> SELECT deptno, AVG(sal)   2  FROM  emp   3  GROUP BY deptno   4  HAVING AVG(sal) >= 2000;

18 HAVING 절 1000이상의 급여를 받는 사원들의 부서별 평균 급여가 2000 이상인 부서 번호와 평균 급여 출력
SQL> SELECT deptno, AVG(sal) 2     FROM  emp 3     WHERE sal >= 1000 4     GROUP BY deptno 5    HAVING AVG(sal) >= 2000; Where 절에는 단순 컬럼, Having 절에는 그룹함수가 적용된 컬럼이 옴.

19 HAVING 절 HAVING 절을 사용한 행 제한
직책이 clerk이 아닌 사원에 대한 부서별 급여 합이 6500 이상인 부서와 급여 합 반환. SQL> SELECT deptno, SUM(sal) 2 FROM emp 3 WHERE job <> ‘CLERK' 4 GROUP BY deptno 5 HAVING SUM(sal) >= 6500; 부서별 최대급여가 4000 이상인 부서의 부서번호와 평균급여 출력. SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING MAX(sal) >= 4000;

20 HAVING 절 HAVING 절을 사용한 행 제한 SELECT deptno, SUM(sal) FROM emp
GROUP BY 절과 HAVING 절에 컬럼 별명 사용불가.(WHERE 절도) GROUP BY 절에 기술된 컬럼으로 오름차순 정렬되어 반환되는 행의 순서를 바꾸기 원한다면 ORDER BY 절 사용. ORDER BY 절에는 표현식(그룹 함수), 컬럼 별명, 컬럼 순서 번호 등이 가능. SELECT deptno, SUM(sal) FROM emp WHERE job <> ‘CLERK' GROUP BY deptno HAVING SUM(sal) >= 6500 ORDER BY SUM(sal);