향상된 group by(그룹함수)

--향상된 group 함수-------------------------------------------------------------

--일반 group by

select department_id, job_id, sum(salary)

from employees

where department_id < 60

group by (department_id, job_id)

order by department_id;

 

--rollup group by

select department_id, job_id, sum(salary)

from employees

where department_id < 60

group by rollup(department_id, job_id);

 

--cube group by

select department_id, job_id, sum(salary)

from employees

where department_id < 60

group by cube(department_id, job_id);

 

--grouping (1: group by를 하지 않음 // 0: group by 적용됨)

select department_id, job_id, sum(salary), grouping(department_id) grp_derpt, grouping(job_id) grp_job

from employees

where department_id < 60

group by cube(department_id, job_id);

 

--grouping sets

select department_id, job_id, manager_id, avg(salary), 

  grouping(department_id) grp_derpt, grouping(job_id) grp_job, grouping(manager_id) grp_man

from employees

group by grouping sets

    ((department_id, job_id), (job_id, manager_id));