[Oracle SQL] GROUP BY, HAVING, ROLLUP

/* Group by 절 */
 
select distinct(department_id) 
from employees;
 
 
select department_id 
from employees 
group by department_id;
 
 
 
/* 부서 별 급여 합계 */
 
select department_id, sum(salary) 
from employees 
group by department_id;
 
 
 
/* 단일 그룹이 아니므로 에러 */
 
select distinct(department_id), sum(salary) 
from employees; 
 
 
 
/* 부서 별 사원수와 급여 평균 */
 
select department_id, sum(salary), count(salary), avg(salary)
from employees
group by department_id;
 
 
 
/*  부서별 직급별 사원수와 급여 평균 */
 
select department_id, job_id, sum(salary), count(salary), avg(salary)
from employees
group by department_id, job_id
order by department_id, job_id;
 
 
 
select department_id, job_id,
   to_char(sum(salary),'999,999') "총급여",
   to_char(avg(salary),'999,999') "평균급여"
from employees
where department_id = 80
group by department_id, job_id
order by department_id, job_id;
 
 
 
/* 현재 부서 별 사원수 */
 
select department_id, count(*) 
from employees
where department_id is not null
group by department_id;
 
 
 
/* Where절에서는 집계함수 사용 불가 */
 
select department_id, count(*)
from employees
where department_id is not null
    and count(*) < 10
group by department_id;  
 
 
 
/* Having 절 */
 
select department_id, count(*) 
from employees
where department_id is not null
group by department_id
Having count(*) >=10;
 
 
 
/* Rollup : 그룹별 합계 정보를 추가해 보여주는 함수 */
 
select l.city, d.department_name, e.job_id,
  count(*) "사원수", sum(e.salary) 총급여
from employees e, departments d, locations l
where e.department_id = d.department_id
  and d.location_id = l.location_id
group by l.city, d.department_name, e.job_id
order by l.city, d.department_name, e.job_id;
 
 
 
select l.city, d.department_name, e.job_id,
  count(*) "사원수", sum(e.salary) 총급여
from employees e, departments d, locations l
where e.department_id = d.department_id
    and d.location_id = l.location_id
group by rollup(l.city, d.department_name, e.job_id)
order by l.city, d.department_name, e.job_id;