having 그룹함수조건절

group by 절을 사용하여 그룹함수의 값을 나타내었을때
       그룹함수의 값이 특정 조건에 해당하는 것만 추출하고자 할때는 where 절을 사용하는 것이 아니라
       having 그룹함수조건절 을 사용해야 한다.

 

 

---- employees 테이블에서 사원이 10명 이상 근무하는 부서번호와 그 인원수를 나타내기 ----

	select department_id 부서번호, count(*) 인원수
    from employees
    group by department_id
    having count(*) >= 10
    order by 2;

 

---- employees 테이블에서 부서번호별로 벌어들이는 salary 의 합계가 50000 이상인 부서에 대해서만
    ---- 부서번호, 기본급여합계를 나타내기 ----

select department_id 부서번호
         , sum(salary) 기본급여합계
    from employees
    group by department_id
    having sum(salary) >= 50000
    order by 2 desc;

 

---- employees 테이블에서 부서번호별로 벌어들이는 월급의 합계가 40000 이상인 부서에 대해서만
    ---- 부서번호, 월급합계를 나타내기 ----

select department_id 부서번호
         , sum( NVL(salary+salary*commission_pct, salary) ) 월급합계
    from employees
    group by department_id
    having sum( NVL(salary+salary*commission_pct, salary) ) >= 40000
    order by 2 desc;

 

---- employees 테이블에서 부서번호별 월급의 합계와 함께 등수(석차)도 나타내기

select department_id 부서번호
         , sum( NVL(salary+salary*commission_pct, salary) ) 월급합계
         , round( sum( NVL(salary+salary*commission_pct, salary) )/(select sum( NVL(salary+salary*commission_pct, salary) ) from employees) * 100 , 1)퍼센티지
         -- 부서번호별 월급의 합계 / 모든 사원들의 월급의 합계
         , rank() over(order by sum( NVL(salary+salary*commission_pct, salary) ) desc) 석차
    from employees
    group by department_id
    order by 2 desc;

 

---

 

---- [퀴즈] employees 테이블에서 부서번호별 월급의 합계에 대한 퍼센티지가 5% 이상인 부서만
    ----       부서번호, 월급의 합계, 퍼센티지를 나타내기
   

/*
    select 부서번호, 월급합계
    from employees
    group by department_id
    having sum(월급)/(모든사원들의월급합계)*100 >= 5
    */



select department_id 부서번호
         , sum( NVL(salary+salary*commission_pct, salary) ) 월급합계
         , round( sum( NVL(salary+salary*commission_pct, salary) )/(select sum( NVL(salary+salary*commission_pct, salary) ) from employees) * 100 , 1)퍼센티지
         -- 부서번호별 월급의 합계 / 모든 사원들의 월급의 합계
    from employees
    group by department_id
    having round( sum( NVL(salary+salary*commission_pct, salary) )/(select sum( NVL(salary+salary*commission_pct, salary) ) from employees) * 100 , 1) >= 5.0
    order by 2 desc;
    
    select department_id AS 부서번호
         , sum_month_sal AS 월급합계
         , pct_sum_month_sal AS 퍼센티지
    from
    (
        select department_id
             , SUM( NVL(salary+salary*commission_pct, salary) ) as sum_month_sal
             , round( SUM( NVL(salary+salary*commission_pct, salary) ) / ( select sum( NVL(salary+salary*commission_pct, salary) ) from employees ) * 100, 1 ) pct_sum_month_sal
        from employees
        group by department_id
    ) V
    where pct_sum_month_sal >= 5
    order by 2 desc;