[Oracle PL-SQL 예제] SQL 함수(집합함수, AVG,COUNT,MAX,MIN,SUM)

[정민이]

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

행(ROW)들의 집합에 대해 연산을 하는 것이므로 행들의 집합 수 만큼 결과가 반환된다. 테이블은 GROUP BY절에 의해 그룹으로 나누어 질 수 있으며 그룹함수(Aggregate Function)는 SELECT문과 HAVING절에 사용되어 질 수 있다. HAVING절은 GROUP BY 되는 그룹에 조건을 주기 위해서 사용 되며 WHERE 절을 이용해서는 안된다.
GROUP BY절을 이용하여 한 테이블에서 행들을 원하는 그룹으로 나누는 것이 가능하며 컬럼 명을 집합 함수와 SELECT절에 이용하고자 한다면 GROUP BY 뒤에 컬럼을 추가 해야 한다. 즉 SELECT절에 그룹함수가 오면 SELECT절의 나머지 컬럼은 GROUP BY절에 나타나야 한다. 또한 GROUP BY절에는 컬럼의 위치 순서 표기(1, 2, 3,,,등등)나 컬럼 Alias는 사용 할 수 없다.


[형식]
SELECT column, group function
FROM TABLE
[WHERE condition]
[GROUP BY group by expression‎]
[HAVAING group condition]
[ORDER BY columns]

AVG

AVG([DISTINCT|ALL n] : n의 평균값을 반환한다.

SQL> create table sawon (
  2  name varchar2(10) not null,
  3  sal number(8,0),
  4  buseo varchar2(10)
  5  );

테이블이 생성되었습니다.

SQL> insert into sawon values ('가길동',2000000,'영업부');
SQL> insert into sawon values ('나길동',3000000, '영업부');
SQL> insert into sawon (name, buseo) values ('다길동','영업부');
SQL> insert into sawon values ('라길동',1000000,'관리부');
SQL> insert into sawon values ('마길동',2000000, '관리부');

 5명의 사원을 이름, 급여, 부서를 입력했다. “다길동” 사원은 급여를 입력하지 않았음에 유의하자. 입력하지 않았으므로 sal 컬럼은 NULL 값으로 채워져 있을 것이다. NULL값은 아무것도 없다는 뜻이며 어떠한 연산(+,-,*,/)을 하여도 NULL이 된다. NULL 컬럼인지를 알기 위해서도 마찬가지로 IS NULL을 사용해서 확인 해야 한다.

SQL>commit;
커밋이 완료되었습니다.
SQL> select name, sal, buseo from sawon;

NAME              SAL BUSEO
---------- ---------- ----------
가길동        2000000 영업부
나길동        3000000 영업부
다길동                영업부
라길동        1000000 관리부
마길동        2000000 관리부

방금 입력한 5명의 사원들의 급여 평균은 얼마일까? 총합은 8000000원 이며 5명이까… 1600000 정도가 되리라고 생각이 들것이다. 아래를 보자.

SQL> select avg(sal) from sawon;

  AVG(SAL)
----------
2000000

 sal 컬럼이 NULL인 “다길동”은 계산에서 제외 된 것이다.  즉 NULL인 컬럼은 계산을 위한 대상에서 제외된 것 이다. 다음 예문은 이를 해결 한 것이다.

SQL> select avg(nvl(sal, 0)) from sawon;

AVG(NVL(SAL,0))
---------------
        1600000

 아래는 부서별로  GROUP BY하여 부서별 평균을 출력 하는 예문이다.

SQL> select buseo, avg(sal) from sawon
  2  group by buseo;

BUSEO        AVG(SAL)
---------- ----------
관리부        1500000
영업부        2500000

 SELECT절의 어떤 컬럼 이나 표현식도 집합 함수가 아니라면 GROUP BY절에 나타나야 한다.

SQL> select buseo, avg(sal) from sawon;
select buseo, avg(sal) from sawon
      *
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

 아래는 부서별로  GROUP BY하여 부서별 평균을 출력 하는데 그 평균이 2000000 보다 큰 부서와 그 평균을 출력하는 예문 이다.

SQL> select buseo, avg(sal) from sawon
  2  group by buseo
  3  having avg(sal) > 2000000;

BUSEO        AVG(SAL)
---------- ----------
영업부        2500000

 아래는 EMP 테이블에서 부서별로 급여의 최대를 출력하는데, 부서코드에 따라 오름차숨으로 표시 하는 예문 이다.
SQL> select deptno, max(sal) "급여 최대(부서)"
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO 급여 최대(부서)
---------- ---------------
        10            5000
        20            3000
        30            2850
[실습 4-23 AVG]














COUNT

COUNT({* | [DISTINCT|ALL] expr}) : 추출된 행(ROW)수를 반환한다. DISTINCT를 사용 했다면 중복된 행을 제거한 행수를 반환 한고 ,expr을 사용 했다면 expr이 NOT NULL인 행의 수를 반환한다. ASTERISK(*)는 중복 과 NULL을 포함한 모든 행의 수를 반환 할 때 사용한다.

 sawon 테이블은 실습4-23에서 생성 하였으니 참고 하기 바란다.

SQL> select name, sal, buseo from sawon;

NAME              SAL BUSEO
---------- ---------- ----------
가길동        2000000 영업부
나길동        3000000 영업부
다길동                영업부
라길동        1000000 관리부
마길동        2000000 관리부

 “다길동” 사원은 sal를 등록하지 않았으니 NULL 임을 기억하자.

SQL> select count(sal) from sawon;

COUNT(SAL)
----------
        4

SQL> select count(distinct sal) from sawon;

COUNT(DISTINCTSAL)
------------------
                3
 아래의 ASTERISK(*)는 결국 테이블등의 전체 건수를 파악 할 때 자주 사용 된다.

SQL> select count(*) from sawon;

  COUNT(*)
----------
        5

 다음 예문은 부서별로 인원수를 출력 하는 예문이다.
SQL> select buseo, count(buseo)||'명'  from sawon
  2  group by buseo;

BUSEO      COUNT(BUSEO)||'명'
---------- -----------------------------------------
관리부    2명
영업부    3명

 각 직무별로 최대급여와 인원수를 구하되 직무별 인원수가 2명 이상인 직무
만 나타내는 예문이다. (평균급여의 소수점 이하는 반올림)

SQL> select job "직무", round(max(sal)) "최대 급여", count(*) "인원수"
  2  from emp
  3  group by job
  4  having count(*)>2;

직무      최대 급여      인원수
--------- ----------  ----------
CLERK            1300          4
MANAGER        2975          3
SALESMAN        1600          4
[실습 4-24 COUNT]

MAX, MIN

MAX, MIN함수는 숫자, 문자, 날짜 등 어떠한 자료형이라도 사용이 가능하다.


MAX([DISTINCT|ALL] expr) : 최대값을 반환한다.
MIN([DISTINCT|ALL] expr) : 최소값을 반환한다.

 sawon 테이블은 실습4-23에서 생성 하였으니 참고 하기 바란다. 아래는 sawon 테이블에서 부서별로 급여의 최대, 최소값을 구하는 예문이다.

SQL> select buseo, max(sal) from sawon
2  group by buseo;

BUSEO        MAX(SAL)
---------- ----------
관리부        2000000
영업부        3000000

SQL> select buseo, min(sal) from sawon
  2  group by buseo;

BUSEO        MIN(SAL)
---------- ----------
관리부        1000000
영업부        2000000


SUM

SUM([DISTINCT|ALL] n) : 합계를 반환한다. 인수 n은 테이블의 숫자형 컬럼 이어야 하며 값에 NULL이 포함되어 있을 때는 합계에 포함되지 않는다.

 sawon 테이블은 실습4-23에서 생성 하였으니 참고 하기 바란다. 아래는 sawon 테이블에서 급여의 총합, 부서별로 급여의 합을 구하는 예문이다.
SQL> select sum(sal) from sawon;

  SUM(SAL)
----------
8000000

SQL> select buseo, sum(sal) from sawon
  2  group by buseo;

BUSEO        SUM(SAL)
---------- ----------
관리부        3000000
영업부        5000000

아래 예문은 부서별로 급여의 총합을 출력 하는데 부서의 급여 평균이 1500000원 보다 큰 경우만 출력하는 예문이다.
SQL> select buseo, sum(sal) from sawon
  2  group by buseo
  3  having avg(sal) > 1500000;

BUSEO        SUM(SAL)
---------- ----------
영업부        5000000