SQL)강의- 제 6 장 GROUP 함수

출처 : http://naver.kaist.ac.kr/oracle_sql/s06.htm

 

1. 그룹함수

◈그룹함수는 그룹당 하나의 결과를 return한다.
◈그룹함수들은 SELECT 및 HAVING절에 쓸 수 있다.
◈SELECT 문장상의 GROUP BY절은 행들을 그룹화한다.
◈ HAVING절은 그룹을 제외한다.
 

♣ Syntax

SELECT      column,group_function
FROM        table
[WHERE      조건]
[GROUP BY   group_by_expression]
[ORDER BY   column];

 

 ♣ 그룹함수

함       수	설           명
AVG(distinct|all|n)	NULL값을 제외한 n개 행의 평균값
COUNT(distinct|all|expr|*)	행의 개수. expr은 NULL값이 아닌 것을 평가한다. 사용하여 중복및 NULL값을 갖는 행들도 계산한다.
MAX(distinct|all|expr)	최대값
MIX(distinct|all|expr)	최소값
STDDEV(distinct|all|n)	NULL값을 제외한 n의 표준편차
SUM(distinct|all|n)	NULL값을 제외한 n의 합계
VAR(distinct|all|n)	NULL값을 제외한 n의 분산

◈DISTINCT는 해당함수로 하여금 오직 중복되지 않는 값만 출력한다.
◈그러나 all은 해당함수로 하여금 중복된 것을 포함한 모든 값을 출력한다.(기본값은 all이다.)
◈expr이 있는 인수들의 datatype은 CHAR, VARCHAR2, NUMBER 또는 DATE형이 될 수 있다.
◈COUNT(*)를 제외한 모든 그룹함수들은 NULL값을 무시한다.

♠실습 : 모든 영업사원에 대해 평균 월급여액, 최고 월급여액, 최저 월급여액 및 합계를 출력하시오. 

SQL> SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)           FROM   s_emp;

Note : 숫자 datatype column에 대해서는 AVG와 SUM함수의 사용이 가능하다.
 

♠실습 : 부서번호 31에 근무하는 사원의 수를 출력하시오. 

SQL> SELECT COUNT(*)  FROM   s_emp   WHERE  dept_id = 31;

 

2. Group BY절

♣ Syntax

SELECT      column,group_function
FROM        table
[WHERE      조건]
[GROUP BY   group_by_expression]
[ORDER BY   column];

◈Group BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눈다.
◈Column명을 Group함수와 SELECT절에 사용하고자 하는 경우 Group BY뒤에 Column명을 추가한다.
◈ORDER BY절을 사용하여 기본 정렬 순서를 바꾼다.
◈기본적으로 행들은 GROUP BY에 명시된 column의 순서대로 올림차순 정렬이 일어난다.
◈Group BY뒤에 오는 column이 반드시 SELECT절에 있어야 되는 것은 아니다.
◈SELECT list 상에서 어떠한 컬럼이나 표현식도 그룹 함수가 아닌 경우에는 Group BY절에 와야 한다.
◈그룹을 제한하기 위해 WHERE절을 사용할 수 없다.(그룹을 제한하기 위해 HAVING절을 사용한다
◈Group BY 다음에 1개 이상의 column을 나열함으로써 그룹 or sub그룹에 대한 결과를 얻을 수 있다.
 

♠실습 : 가능한 고객신용도 및 각 신용도 범주 내에 있는 고객들의 수를 출력하시오.

SQL> SELECT credit_rating, COUNT(*) "고객신용도"  FROM   s_customer           GROUP BY credit_rating

 

♠실습 : 직급과 직급별 총 월급여액을 출력하되 부사장은 제외하고 총 월급여액순으로 출력하시오.

SQL> SELECT title,SUM(salary) PAYROLL    FROM   s_emp          WHERE  title NOT LIKE 'VP%'          GROUP BY title   ORDER BY SUM(salary);

 

♠실습 : 각 직급 그룹별로 최고 급여액과 직급을 출력하시오.

SQL> SELECT title,MAX(salary)  FROM   s_emp          GROUP BY title;

 

♠실습 : 각 지역과 각 지역의 부서 수를 출력하시오.

SQL> SELECT region_id,COUNT(name)  FROM   s_dept          GROUP BY region_id

 

♠실습 : 각 부서 내에서 각 직급 범주별로 직원 수를 출력하는 예

SQL> SELECT dept_id,title,COUNT(*)      FROM   s_emp          GROUP BY dept_id,title;

 

3. GROUP함수를 잘못 사용한 Query

◈SELECT상에서 어떤한 column이나 표현식도 그룹함수가 아닌 경우에는 GROUP BY절에 와야 한다.
◈그룹을 제한하기 위해 WHERE절을 사용할 수 없고 HAVING절을 사용하여야 한다.
◈SELECT문장에서 각각의 항목(아래에서 REGION_ID)과 그룹함수(아래 예의 COUNT)를복합해서
   사용시 각 항목을 명시하는 GROUP BY절을 포함해야 한다.
◈GROUP BY절 생략시 "not a single-group group funtion"이란 error message가 나타나고
    애스테리스크(*)가 위반한 Line을 지적해 준다.

♠실습 : 각 지역내에서 부서의 수를 Display한다.

SQL> SELECT   region_id, COUNT(name)     FROM     s_dept           GROUP BY  region_id;

 

♠실습 : 평균 급여액이 2000이 넘는 부서에 대해 부서번호 및 평균급여액을 Display한다.

SQL> SELECT   dept_id, AVG(salary)     FROM     s_emp           GROUP BY dept_id           HAVING   AVG(salary) > 2000

 

4. 그룹내의 그룹

♠실습 : 각 부서내에서 각 직급 범주별로 직원수를 Display한다.

SQL> SELECT dept_id, title, COUNT(*)     FROM   s_emp           GROUP BY dept_id, title;

 

♠실습 : 평균 급여액이 2000이 넘는 부서에 대해 부서번호 및 평균급여액을 Display한다.

SQL> SELECT title, dept_id, COUNT(*)    FROM   s_emp          GROUP BY title, dept_id;

 

 5. HAVING절

♣ Syntax

SELECT      column,group_function
FROM        table
[WHERE      조건]
[GROUP BY   group_by_expression]
[HAVING     group_condition]
[ORDER BY   column];
 

◈여기서, group_condtion은 그룹을 제한하기 위한 조건이다.
◈그룹 제한시 HAVING절을 사용한다.
    -- Step 1 : 행들이 그룹핑된다.
    -- Step 2 : 그룹함수가 그룹에 적용된다.
    -- Step 3 : HAVING조건을 만족하는 그룹들이 display된다.


◎ HAVING절을 사용하면 SELECT상에서 그룹함수 없이 Group BY절을 사용할 수 있다.
◎ 그룹함수의 결과에 근거한 그룹 제한시 GROUP BY절 및 HAVING절을 사용한다.

♠실습 : 월급여액이 4000이 넘는 부서들에 대해 모든 부서번호를 출력하시오. 

SQL> SELECT dept_id,AVG(salary)           FROM   s_emp           GROUP BY dept_id           HAVING AVG(salary) > 2000;

 

♠실습 : 총 월급여액이 5000이 넘는 직급에 대해 직급명과 총 월급여액을 출력하시오.
            단, 부사장은 포함시키지 말고 결과를 총월급여액 순으로 정렬하시오.

SQL> SELECT title,SUM(salary) "급여"              FROM   s_emp          WHERE  title NOT LIKE 'VP%'          GROUP BY title          HAVING SUM(salary) > 5000          ORDER BY SUM(salary);