subtotal,cube,grouping_id

1. 참조사이트: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

 

2. Oracle 
 - URL : https://livesql.oracle.com/apex/f?p=590:1:107662273582818::NO:::

 

 

select department_id, gubun, gubun2, sum(salary) as sum_salary
  from employees
 group by department_id, cube(gubun, gubun2)
 order by 1, 2, 3
; 

-- subtotal  
select department_id, gubun, gubun2, sum(salary) as sum_salary, grouping(gubun) as f1, grouping(gubun2) as f2,  grouping_id(department_id, gubun, gubun2) as g1
  from employees
 group by cube(department_id, gubun, gubun2)
 having grouping_id(department_id, gubun, gubun2) not in (1, 3, 4, 5, 7)
 order by 1, 2, 3
; 

-- subtotal
with tb_a as (
select decode(grouping_id(department_id, gubun, gubun2), 6, 'total:', department_id) as department_id
     , decode(grouping_id(department_id, gubun, gubun2), 2, 'subtotal:'|| department_id, gubun) as gubun
     , gubun2
     , sum(salary) as sum_salary
     , grouping(gubun) as f1
     , grouping(gubun2) as f2
     , grouping_id(department_id, gubun, gubun2) as g1
  from employees
 group by cube(department_id, gubun, gubun2)
 having grouping_id(department_id, gubun, gubun2) not in (1, 3, 4, 5, 7)
 order by 1, 2, 3
)
select *
  from tb_a a
 where g1 = 6
union all
select *
  from tb_a a
 where g1 != 6
; 

DEPARTMENT_ID	GUBUN	GUBUN2	SUM_SALARY	F1	F2	G1
total:	 - 	하위1	33200	1	0	6
total:	 - 	하위2	42200	1	0	6
total:	 - 	하위3	10100	1	0	6
total:	 - 	하위4	20000	1	0	6
10	subtotal:10	하위1	30000	1	0	2
10	subtotal:10	하위2	40000	1	0	2
10	subtotal:10	하위3	10000	1	0	2
10	구분1	하위1	20000	0	0	0
10	구분1	하위2	20000	0	0	0
10	구분2	하위1	10000	0	0	0
10	구분2	하위2	20000	0	0	0
10	구분2	하위3	10000	0	0	0
20	subtotal:20	하위4	20000	1	0	2
20	구분1	하위4	20000	0	0	0
30	subtotal:30	하위1	3000	1	0	2
30	구분B	하위1	3000	0	0	0
40	subtotal:40	하위2	2000	1	0	2
40	구분B	하위2	2000	0	0	0
50	subtotal:50	하위1	200	1	0	2
50	subtotal:50	하위2	200	1	0	2
50	subtotal:50	하위3	100	1	0	2
50	구분1	하위2	200	0	0	0
50	구분3	하위1	200	0	0	0
50	구분3	하위3	100	0	0	0

  
select department_id, gubun, gubun2, sum(salary) as sum_salary
     --, grouping(gubun) as f1
     --, grouping(gubun2) as f2
     , grouping_id(department_id, gubun, gubun2) as grouping_id
     , group_id() as group_id
  from employees
 group by grouping sets(department_id, cube(department_id, gubun, gubun2))
 -- having grouping_id(department_id, gubun, gubun2) not in (1, 3, 4, 5, 7)
 order by 1, 2, 3
;