ROLLUP:

 ROLLUP:

******

-->Rollup is calculating subtotals and grand totals 

-->ROLLUP extension produces group subtotals from right to left and a grand total

SELECT DEPTNO,SUM(SAL)

FROM EMP

GROUP BY DEPTNO;

/

SELECT DEPTNO,SUM(SAL)

FROM EMP

GROUP BY ROLLUP(DEPTNO);

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL

FROM EMP

GROUP BY DEPTNO,JOB

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL

FROM EMP

GROUP BY ROLLUP(DEPTNO,JOB)

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL

FROM EMP

GROUP BY ROLLUP(DEPTNO,JOB)

ORDER BY 1,2;

/

grouping id functionality:

************************

both column values are not null --grouping id it will print 0

one column value is not null and one column value is null  --1

one column value is null and seond column value is not null --2

both columns values is null    --3

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL,

       GROUPING_ID(DEPTNO,JOB)SUBTOTAL

FROM EMP

GROUP BY ROLLUP(DEPTNO,JOB)

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL,

       CASE GROUPING_ID(DEPTNO,JOB)

       WHEN 1 THEN 'DEPT SUBTOTAL' 

       WHEN 2 THEN 'JOB SUBTOTAL'

       WHEN 3 THEN 'GRAND TOTAL'

       END SUBTOTAL_GRANDTOTAL

FROM EMP

GROUP BY ROLLUP(DEPTNO,JOB)

ORDER BY 1,2;

/

--AP_INVOICES_ALL TABLE USING ROLLUP

/

SELECT INVOICE_NUM,SUM(INVOICE_AMOUNT)

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10;

GROUP BY ROLLUP(INVOICE_NUM);

/

SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,SUM(INVOICE_AMOUNT)

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10

GROUP BY ROLLUP(INVOICE_NUM,INVOICE_CURRENCY_CODE)

ORDER BY INVOICE_NUM;

/

SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,SUM(INVOICE_AMOUNT),

       GROUPING_ID(INVOICE_NUM,INVOICE_CURRENCY_CODE)

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10

GROUP BY ROLLUP(INVOICE_NUM,INVOICE_CURRENCY_CODE)

ORDER BY INVOICE_NUM;

/

SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,SUM(INVOICE_AMOUNT),

       CASE GROUPING_ID(INVOICE_NUM,INVOICE_CURRENCY_CODE)

          WHEN 1 THEN 'INVOICE SUBTOTAL'

          WHEN 2 THEN 'CURRENCY SUBTOTAL'

          WHEN 3 THEN 'GRAND TOTAL'

       END   

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10

GROUP BY ROLLUP(INVOICE_NUM,INVOICE_CURRENCY_CODE)

ORDER BY INVOICE_NUM;

/

CUBE:

*****

-->CUBE extension will generate subtotals for all combinations of the dimensions specified.

EX:

***

SELECT DEPTNO,SUM(SAL)

FROM EMP

GROUP BY DEPTNO;

/

SELECT DEPTNO,SUM(SAL)

FROM EMP

GROUP BY CUBE(DEPTNO);

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL

FROM EMP

GROUP BY DEPTNO,JOB

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL

FROM EMP

GROUP BY CUBE(DEPTNO,JOB)

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL

FROM EMP

GROUP BY CUBE(DEPTNO,JOB)

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL,

       GROUPING_ID(DEPTNO,JOB)SUBTOTAL

FROM EMP

GROUP BY CUBE(DEPTNO,JOB)

ORDER BY 1,2;

/

SELECT DEPTNO,JOB,SUM(SAL) SUMSAL,

       CASE GROUPING_ID(DEPTNO,JOB)

       WHEN 1 THEN 'DEPT SUBTOTAL' 

       WHEN 2 THEN 'JOB SUBTOTAL'

       WHEN 3 THEN 'GRAND TOTAL'

       END SUBTOTAL_GRANDTOTAL

FROM EMP

GROUP BY CUBE(DEPTNO,JOB)

ORDER BY 1,2;

/

--AP_INVOICES_ALL TABLE USING CUBE

/

SELECT INVOICE_NUM,SUM(INVOICE_AMOUNT)

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10;

GROUP BY CUBE(INVOICE_NUM);

/

SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,SUM(INVOICE_AMOUNT)

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10

GROUP BY CUBE(INVOICE_NUM,INVOICE_CURRENCY_CODE)

ORDER BY INVOICE_NUM;

/

SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,SUM(INVOICE_AMOUNT),

       GROUPING_ID(INVOICE_NUM,INVOICE_CURRENCY_CODE)

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10

GROUP BY CUBE(INVOICE_NUM,INVOICE_CURRENCY_CODE)

ORDER BY INVOICE_NUM;

/

SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,SUM(INVOICE_AMOUNT),

       CASE GROUPING_ID(INVOICE_NUM,INVOICE_CURRENCY_CODE)

          WHEN 1 THEN 'INVOICE SUBTOTAL'

          WHEN 2 THEN 'CURRENCY SUBTOTAL'

          WHEN 3 THEN 'GRAND TOTAL'

       END   

FROM AP_INVOICES_ALL

WHERE ROWNUM<=10

GROUP BY CUBE(INVOICE_NUM,INVOICE_CURRENCY_CODE)

ORDER BY INVOICE_NUM;

/