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

이번 포스팅에서는 집계 함수중 가장 많이 사용되는 SUM( ), MAX( ), MIN( ), AVG( ), COUNT( )에 대해서 포스팅하겠습니다. 집계 함수의 기본 사용법, GROUP BY를 사용한 사용법 OVER ( )을 통한 사용법을 소개하겠습니다.

1. 개요

---

집계 함수란 ?

• 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수를 뜻합니다.
• GROUP BY절을 이용하여 그룹화, HAVING 절을 이용하여 그룹에 대한 조건 비교가 가능합니다.
• MIN( ) , MAX ( ) 함수는 모든 자료형에 사용 가능합니다.
• 집계 함수는 NULL을 체크하지 않습니다. 단! COUNT(*) 의 경우 NULL도 포함한 값을 반환합니다.

2. 기본 사용방법

---

실습에 앞서 사용될 테이블의 데이터입니다.

COUNT ( ) 검색된 행의 수를 반환합니다.

SELECT COUNT(*) FROM VIEW_EMPINFO;

COUNT(*)
--------------
            11

SELECT COUNT(DEPT_NO) FROM VIEW_EMPINFO;

COUNT(DEPT_NO)
--------------
            10

여기서 기억해둬야 할 부분은 위에서 언급했지만 COUNT(*)의 경우 NULL도 포함한 개수를 반환하는 예외라고 했던 부분입니다. 그래서 COUNT(*)의 경우 11을 리턴 COUNT(DEPT_NO)의 경우 NULL을 제외한 개수인 10개를 리턴합니다.

SUM( ) 검색 된 행의 모든 합을 반환합니다.

SELECT SUM(SAL) FROM VIEW_EMPINFO;

SUM(SAL)
------------
        2600

모든 직원의 급여 합계를 구합니다. 여기서 SUM은 합을 리턴하기 때문에 NUMBER타입의 컬럼을 사용해야 합니다.

MAX ( ) 검색된 행중에서 최대 값을 반환합니다.

SELECT MAX(SAL) FROM VIEW_EMPINFO;

MAX(SAL)
-------------
          400    


SELECT MAX(DEPT_NO) FROM VIEW_EMPINFO;

MAX(DEPT_NO)
-------------
           40


SELECT MAX(EMP_NAME) FROM VIEW_EMPINFO;

MAX(EMP_NAME)
-------------
        홍길동     

MAX ( ) 는 모든 타입에 사용이 가능하기 때문에 문자형이 EMP_NAME에 대해서도 최대 값을 리턴해줍니다.

MIN ( ) 검색 된 행중에서 최소 값을 반환합니다.

SELECT MIN(SAL) FROM VIEW_EMPINFO;

MAX(SAL)
-------------
           50    

SELECT MIN(DEPT_NO) FROM VIEW_EMPINFO;

MAX(DEPT_NO)
-------------
           10


SELECT MIN(EMP_NAME) FROM VIEW_EMPINFO;

MAX(EMP_NAME)
-------------
        JAMES     

MIN () 는모든 타입에 사용이 가능하기 때문에 문자형이 EMP_NAME에 대해서도 최소 값을 리턴해줍니다.

AVG ( ) 검색 된 행중에서 최소 값을 반환합니다.

SELECT AVG(SAL) FROM VIEW_EMPINFO;

AVG(SAL)
----------------
236.363636363636

전체 직원의 평균 급여를 계산해서 리턴해줍니다.

3. 집계 결과를 GROUP BY절을 이용여 그룹화 하기

---

사용 방법은 동일하기 때문에 AVG( ) 함수를 기준으로 설명하겠습니다.
부서별로 급여의 평균을 구하고 싶을 때 아래와 같이 사용합니다.
기준이 되는 컬럼을 GROUP BY 절에 명시를 해주어서 그룹화를 시킵니다.

SELECT DEPT_NO, AVG(SAL) 
FROM VIEW_EMPINFO
GROUP BY DEPT_NO;

DEPT_NO, AVG(SAL) 
-------------------
   NULL     400
     30     250
     40     350 
     20   187.5
     10      75

여기서 부서별 또는 급여별로 정렬을 주고 싶은 경우 ORDER BY절을 마지막에 추가해주면 원하는 기준으로
정렬을 할 수 있습니다.

4. GROUP BY 절이 아닌 OVER ( ) 을 이용하여 그룹화 하기

---

일반적으로 아래와 같이 사용하면 GROUP BY 절을 이용해 그룹화를 해주지 않았기 때문에
[ ORA-00937 : 단일 그룹의 그룹 함수가 아닙니다. ] 라는 오류를 발견할 겁니다.

SELECT DISTINCT DEPT_NO, SUM ( SAL ) FROM VIEW_EMPINFO; 

이 경우 OVER ( ) 를 사용하여 GROUP BY 절 없이 집계 함수를 사용할 수 있습니다.

SELECT DISTINCT DEPT_NO
      ,SUM(SAL) OVER( PARTITION BY DEPT_NO) 
FROM VIEW_EMPINFO;

DEPT_NO      SUM(SAL)
---------------------
   NULL          400
     10          150
     20          750
     30          250
     40         1050

그룹화할 대상 컬럼을 PARTITION BY 절 다음에 명시해줍니다. 그러면 해당 컬럼을 소그룹 기준으로
결과 값을 리턴해줍니다. 더 나아가 부서별 총 급여액이 아닌 소계를 표시하는 방법을 소개하겠습니다.

SELECT DEPT_NO
      ,SUM(SAL) OVER( PARTITION BY DEPT_NO ORDER BY SAL) 
FROM VIEW_EMPINFO;

DEPT_NO  SUM(SAL)
--------------------
    10         50
    10        150
    20        300    
    20        300
    20        500
    20        750
    30        250
    40        300
    40        650
    40       1050
  NULL        400

위와 같은 방법으로 PARTITON BY 뒤에 ORDER BY를 사용하여 급여별로 정렬을 부여해주면
급여별 합계 누계가 실행 됩니다. 위처럼 WINDOWING 절을 이용하여 다양하게 원하는
결과값을 받아올 수 있습니다.

---

* 추가 아래 쿼리는 위 쿼리와 동일한 실행 방식으로 동작합니다 ( DEFAULT 로 추가 )

SELECT DEPT_NO
      ,SUM(SAL) OVER(PARTITION BY DEPT_NO 
                     ORDER BY SAL RANGE 
                     BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
FROM VIEW_EMPINFO;

자세한 내용은 WINDOW FUNCTION 이란? 을 참고하시면 조금은 도움이 되실거 같습니다.