Pivoting - 3

[debasisdas]

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