CUBE:-IT GENERATES SUBTOTAL FOR ALL POSSIBLE COMBINATION OF GROUPED COLUMNS.
GROUPING SETS:-GENERATES SUMMARY INFORMATION AT THE CHOOSEN LEVEL,WITHOUT INCLUDING ALL THE ROWS PRODUCED BY REGULAR GROUP BY OPERATION.
GROUPING,GROUPING_ID,GROUP_ID :-HELPS TO CORRECTLY INTERPRET RESULTS GENERATED USING ROLLUP,CUBE AND GROUPING SETS. - select empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno);
-
-
select decode(grouping(empno),1,'all empno',empno) as no,empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno);
-
-
-
select decode(grouping(empno),1,'all empno',empno) as no,empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno)
-
-
select empno,ename,avg(sal) from emp group by grouping sets(empno,ename)
The value from GROUPING(JOB) will be 1 or 0 depending on whether or not the values for SAL are due to the GROUP BY or the CUBE. If the results are due to the CUBE, the value will be 1, otherwise it will be 0.
---------------------------------------------------- - select deptno,
-
job,
-
case grouping(deptno)||grouping(job)
-
when '00' then 'TOTAL BY DEPT AND JOB'
-
when '10' then 'TOTAL BY JOB'
-
when '01' then 'TOTAL BY DEPT'
-
when '11' then 'GRAND TOTAL FOR TABLE'
-
end category,
-
sum(sal) sal
-
from emp
-
group by cube(deptno,job)
-
order by grouping(job),grouping(deptno)
Few more sample query
==================
Sample #1
=========== - select deptno, job, sum(sal) sal,
-
grouping(deptno) deptno_subtotals,
-
grouping(job) job_subtotals
-
from emp
-
group by cube(deptno,job)
Sample #2
=========== - select ename,
-
job,
-
case when job = 'CLERK'
-
then 1 else 0
-
end as is_clerk,
-
case when job = 'SALESMAN'
-
then 1 else 0
-
end as is_sales,
-
case when job = 'MANAGER'
-
then 1 else 0
-
end as is_mgr,
-
case when job = 'ANALYST'
-
then 1 else 0
-
end as is_analyst,
-
case when job = 'PRESIDENT'
-
then 1 else 0
-
end as is_prez
-
from emp
-
order by 2
Sample #3
=========== - select max(case deptno when 10 then ename end) d10,
-
max(case deptno when 20 then ename end) d20,
-
max(case deptno when 30 then ename end) d30,
-
max(case job when 'CLERK' then ename end) clerks,
-
max(case job when 'MANAGER' then ename end) mgrs,
-
max(case job when 'PRESIDENT' then ename end) prez,
-
max(case job when 'ANALYST' then ename end) anals,
-
max(case job when 'SALESMAN' then ename end) sales
-
from (
-
select deptno, job, ename,
-
row_number()over(partition by deptno order by empno) rn
-
from emp
-
) x
-
group by rn
Performing Aggregations over Different Groups/Partitions Simultaneously
================================================== ==== - select ename,deptno, count(*)over(partition by deptno) deptno_cnt, job,
-
count(*)over(partition by job) job_cnt,
-
count(*)over() total from emp
Performing Aggregations over a Moving Range of Values
========================================== - select hiredate,
-
sal,
-
sum(sal)over(order by hiredate
-
range between 90 preceding
-
and current row) spending_pattern
-
from emp e
Pivoting a Result Set with Subtotals
============================ - select mgr,
-
sum(case deptno when 10 then sal else 0 end) dept10,
-
sum(case deptno when 20 then sal else 0 end) dept20,
-
sum(case deptno when 30 then sal else 0 end) dept30,
-
sum(case flag when '11' then sal else null end) total
-
from (
-
select deptno,mgr,sum(sal) sal,
-
cast(grouping(deptno) as char(1))||
-
cast(grouping(mgr) as char(1)) flag
-
from emp
-
where mgr is not null
-
group by rollup(deptno,mgr)
-
) x
-
group by mgr
|
