그룹 함수(GROUP BY)

그룹 함수란?

- 단일 행 함수와는 달리 그룹함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수를 말한다. 그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY 절을 이용하여 그룹화 할 수 있고 HAVING 를 이용 하여 그룹에 대한 조건을 제한하는 방법이다.

  그룹 함수의 종류

함   수	설    명
AVG(DISTINCT | ALL | n)	NULL값을 제외한 n개 행의 평균값
COUNT(DISTINCT | ALL | expr | *)	NULL이 아닌 행의 개수
MAX(DISTINCT | ALL | expr)	최대값
MIN(DISTINCT | ALL | expr)	최소값
STDDEV(DISTINCT | ALL | n)	NULL값을 제외한 n의 표준편차
SUM(DISTINCT | ALL | n)	NULL값을 제외한 n의 합계
VARIANCE(DISTINCT | ALL | n)	NULL값을 제외한 n의 분산

1)   DISTINCT는 해당 함수로 하여금 오직 중복되지 않는 값만 RETURN하게 해준다. 그러나 ALL(Default)은 해당 함수로 하여금 모든 값을 고려하게 한다.

2)   Expr이 있는 인수들의 자료 형태는 CHAR, VARCHAR2, NUMBER, DATE형이 될 수 있다.

3)   COUNT(*)를 제외한 모든 그룹 함수들은 NULL값을 무시한다. NULL값을 하나의 값으로 치환하기 위해서는NVL함수를 사용하라.

4)   모든 자료형에 대하여 MAX와 MIN를 사용할 수 있다. 그러나 AVG, SUM, VARIANCE, STDDEV는 NUMBER만 사용 가능하다.

Syntax

SELECT               [column,] group_function(column) [,group_function(column),...]       FROM          table_name       [WHERE        condition]       [GROUP BY     group_by_expression]       [HAVING       condition]       [ORDER BY     column];

table_name   테이블명 질의 대상 테이블 이름

WHERE                condition 을 만족하는 행들만 검색

ORDER BY     질의 결과 정렬을 위한 옵션(ASC:오름차순(Default),DESC내림차순)

group_function                      Group Function이 SELECT절 뒤에서 Column과 같이

기술되면 반드시 GROUP BY절이 기술되어야 한다.

GROUP BY group_by_expression 행을 그룹하기 위한 기준이 될 Column을 기술

HAVING       condition             그룹에 대한 조건을 기술(주의!)

※  SELECT절에 GROUP Function이 포함된다면 GROUP BY절에 각각의 열이 명시되어야 함

※ 그룹 함수에서의 제한조건은 HAVING절에서 기술한다!!

주의사항!!

문제) EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라.

SQL> SELECT deptno,COUNT(*),SUM(sal)   2  FROM emp   3  WHERE COUNT(*) > 4   4  GROUP BY deptno; WHERE COUNT(*) > 4       * ERROR at line 3: ORA-00934: group function is not allowed here

- 위에처럼 WHERE 절에는 GROUP Function을 사용할수 없으며 아래와같이 HAVING절을 사용하여 명시한다.

SQL> SELECT deptno,COUNT(*),SUM(sal)   2  FROM emp   3  GROUP BY deptno   4  HAVING COUNT(*) > 4;      DEPTNO  COUNT(*)  SUM(SAL) --------- --------- ---------        20         5     10875        30         6      9400

종합 문제) EMP 테이블에서 전체 월급이 5000 을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하여라. 단 판매원은 제외하고 월 급여 합계로 정렬(내림차순)하여라.

SQL> SELECT job, SUM(sal) PAYROLL  FROM emp  WHERE job NOT LIKE 'SALE%'  GROUP BY job  HAVING SUM(sal) > 5000  ORDER BY SUM(sal) DESC;    JOB                PAYROLL  -----------       -----------  MANAGER                8275  ANALYST                  6000