12. 그룹함수(집계함수)

1. count
** 사용법 : count(대상)
- 대상에는 * 또는, 숫자, 문자, 날짜값을 갖는 컬럼명
- null은 무시
- null 집합의 count 결과는 0
- null 집합의 sum 결과는 null리턴.
     
예제) 직원 수
select * from emp;
desc emp;  --> not null을 찾아서 카운트 하는것이 가장 빠르고 효과적이다. 
select count(*), count(ename), count(empno), count(comm)
  from emp;


select *
  from emp;


--> count로 테이블 행의 수를 셀 경우 * 보다는 not null 선언된 컬럼 하나를 세는게 가장 효과적!


예) null 집합의 count 결과 ***

select count(comm)
  from EMP
 where comm is null;

2. sum

** 사용법 : sum(대상)

- null은 무시
- null 집합의 sum 결과는 null 리턴

select sum(sal), sum(comm)
  from emp;

예) null 집합의 sum 결과 ***
select sum(comm)
  from EMP
 where comm is null;

 

 

3. min/max


select min(comm), max(comm)
  from emp;

예) null 집합의 min/max 결과 ***
select min(comm),max(comm)
  from EMP
 where comm is null;

 

4. avg

select sum(comm)/count(*), 
       avg(comm),
       avg(nvl(comm,0))
  from emp;
 

 
 
   
 
5. group by
- select문의 4번째 위치
- 그룹별 연산을 위해 사용
  
예제) 부서별 급여 평균

select deptno, round(avg(sal)) as 급여평균
  from EMP
 group by deptno;

 
 예제) 부서별 급여 최대

select deptno, max(sal) as 최대급여, ename
  from EMP
 group by deptno;  --> 에러발생.  ename 가 select 문제 올라갈 수 없다.
 

 

** group by 주의점
- group by에 명시되지 않은 컬럼은 집계함수 없이 select 절, oroder by절, having절에 표현할 수 없음 **
  

다음은 에러가 발생하는 세가지 예시다.

select deptno, sum(sal),sal
  from EMP
 group by deptno;

  

select deptno, sum(sal)
  from EMP
 group by deptno
having sal >= 3000;

 

select deptno, sum(sal)
  from emp
group by deptno
order by sum(comm) desc;              -- 에러(ORA-00979 : GROUP BY 표현식이 아닙니다.)






 6.having 
- group by 이후 수행
- 문법적으로 group by 절과 순서를 변경할 수 있음 
- group by 수행 결과에 조건을 전달하여 원하는 그룹을 선택하기 위해 사용
- 집계함수 사용 가능

예제) emp 테이블에서 부서별 급여평균을 구한 뒤, 급여평균이 2000 이상인 그룹만 출력
select deptno, avg(sal)
  from emp 
 group by deptno
  having avg(sal)>=2000;
만약)  where avg(sal) >=2000 --> 에러 발생 : 그룹함수는 허가되지 않습니다. 


select deptno, avg(sal)
  from emp 
  having avg(sal) 
 group by deptno;        --> 정상(having을 group by보다 먼저 전달 가능)
 
 
 
예제) student 테이블에서 전공별(deptno1) 학생들의 키 평균을 출력
단, 101번 학과는 제외
 
 
 select *
   from student;
 
 
 select deptno1 학과번호,avg(height) 평균키
   from student
  where deptno1 != 101
   group by deptno1;
   
  select deptno1 학과번호,avg(height) 평균키
   from student
   group by deptno1
   having deptno1 != 101;
   

 

두 결과는 같은것을 알 수 있다. 

일반조건(그룹함수를 사용하지 않는)은 where 절을 사용한 조건 처리가 성능에 더 유리!!
이미 제거할 대상을 먼저 제거하고 그룹연산을 수행하는 것이 더 효과적



[ 연습문제 ]

1. professor 테이블을 사용하여 교수와 강사 각각의 급여 평균 출력

sol)   
select substr(position,-2,2) 직급,round(avg(pay)) 급여평균 
  from professor
  group by substr(position,-2,2);
 결과)

 

2. student 테이블에서 3,4학년 학생들에 대해 성별 키평균, 몸무게 평균 출력
select *
  from student;
    

sol)

select decode(substr(jumin,7,1),'1','남자','여자') 성별, round(avg(height)) 키평균, round(avg(weight)) 몸무게평균
  from student
 where grade in (3,4)
 group by decode(substr(jumin,7,1),'1','남자','여자');

 

결과)

3. student 테이블에서 지역별 학생수 출력

 select substr(tel,1,instr(tel,')')-1) 지역 , count(substr(tel,1,instr(tel,')')-1)) 학생수
  from student
 group by substr(tel,1,instr(tel,')')-1)
 order by 1 ;

 

 

정리)

 

 group by 사용하지 않고 집계함수 연산 시 1건 요약 출력**
select sum(sal)
  from emp;

 group by 사용하여 집계함수 연산 시 그룹별 1건 요약 출력**
select deptno, sum(sal) , sal 
  from EMP
 group by deptno;

  
 
예제)  null 집합에 대한 count -> 0, sum -> null을 갖는 한 건 출력
select count(comm),sum(comm)
  from emp
 where comm is null;  

예제) 공집합에 대한 count -> 0, sum -> null을 갖는 한 건 출력
select count(comm),sum(comm)
  from emp 
 where comm<0;

 
  한건으로 반드시 출력되어야 한다.
 ---> 공집합
  
  
예제) having 사용 시 조건에 만족하는 그룹이 없을경우 공집합 출력  
select count(comm), sum(comm)
  from emp 
 group by deptno
having sum(comm)>5000;