[ORACLE 강좌] 오라클 함수 - 집합함수 ( Aggregate functions )

집합함수 ( Aggregate functions )

 

---

집합함수의 정의

: 하나 이상의 행으로부터 일종의 통계정보를 하나의 행으로 반환하는 함수

 

집합함수의 사용

: 보통 GROUP BY 절, HAVING 절과 같이 쓰인다.

Using the GROUP BY Clause

: Examples To return the minimum and maximum salaries for each department in the employees table, issue the following statement:

SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     GROUP BY department_id
   ORDER BY department_id;

To return the minimum and maximum salaries for the clerks in each department, issue the following statement:

SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     WHERE job_id = 'PU_CLERK'
     GROUP BY department_id
   ORDER BY department_id;

 

Using the HAVING Condition: Example To return the minimum and maximum salaries for the employees in each department whose lowest salary is less than $5,000, issue the next statement:

SELECT department_id, MIN(salary), MAX (salary)
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary) < 5000
   ORDER BY department_id;

DEPARTMENT_ID MIN(SALARY) MAX(SALARY)
------------- ----------- -----------
           10        4400        4400
           30        2500       11000
           50        2100        8200
           60        4200        9000

The following example uses a correlated subquery in a HAVING clause that eliminates from the result set any departments without managers and managers without departments:

SELECT department_id, manager_id 
   FROM employees 
   GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN
   (SELECT department_id, manager_id FROM employees x 
      WHERE x.department_id = employees.department_id)
   ORDER BY department_id;

 

 

집합함수의 종류

 

** 대표적인 집합함수 **

AVG                평균값 반환
COUNT            갯수 반환
MAX                최대값 반환
MIN                 최소값 반환
RANK               순위 반환
SUM                합계 반환