[DB] GROUP BY절 과 HAVING절

1. 문법

select column, group_function from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

2. 그룹함수

 -  null값을 무시(null인값은 포함시키지 않음)

 - count만 (*) 사용하여 null값 포함해서 출력됨

AVG (*|[DISTINCT|ALL]expr)	평균
COUNT (*|[DISTINCT|ALL]expr)	행 갯수
MAX (*|[DISTINCT|ALL]expr)	최대값
MIN (*|[DISTINCT|ALL]expr)	최소값
STDDEV (*|[DISTINCT|ALL]n)	표준편차
SUM (*|[DISTINCT|ALL]n)	더하기
VARIANCE (*|[DISTINCT|ALL]n)	분산

select avg(salary), 
       max(salary), 
       min(salary), 
       sum(salary) 
  from emp
 where job_id like '%REP';

  select count(commission_pct) from emp
                              where department_id=80;
  
  
  //중복제거distinct 사용법
  select count(distinct(commission_pct)) from emp
                              where department_id=80;
  

3. GROUP BY 절

 - column에 대한 평균

 - group컬럼을 제외하고는 다른 컬럼이 들어갈수 없음

 

    select department_id, 
           avg(salary) 
      from employees 
  group by department_id;
  //부서에대한 평균 급여

    select department_id, 
           job_id, 
           sum(salary) 
      from employees 
     where department_id > 40
  group by department_id, job_id
 oreder by department_id;
 //department_id 40이상이고  department_id, job_id을 그룹화하여 department_id순으로 출력

4. HAVING절

 - having절은 group by의 조건(where 절은 행의 조건)

 - having절 일반함수 못씀(무조건 group함수)

      select job_id, 
             sum(salary) pay 
        from emp 
       where job_id not like '%REP%'
    group by job_id
       HAVIN SUM(salary) > 13000
   oreder by sum(salary);

 

 

참고. ORACLE DB(부산아이티윌)