Oracle – ROLLUP & CUBE

ROLLUP operators let you extend the functionality of GROUP BY clauses by calculating subtotals and grand totals for a set of columns. The CUBE operator is similar in functionality to the ROLLUP operator; however, the CUBE operator can calculate subtotals and grand totals for all permutations of the columns specified in it.

Note: The result of both the ROLLUP and the CUBE operators will be similar, if your data is grouped by only one column.

Example

Group By Single Column ROLLUP and CUBE

-- General Group By Single Column Query
-- =====================================
SELECT department_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- Group By Single Column ROLLUP
-- ==============================
SELECT department_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY ROLLUP(department_id)
ORDER BY department_id;

-- Group By Single Column CUBE
-- ============================
SELECT department_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY CUBE(department_id)
ORDER BY department_id;

-- Note: Above ROLLUP and CUBE will returns same value.

Output

-- General Group By Single Column Query
-- =====================================
DEPARTMENT_ID SALARY_SUM
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     156400
           60      28800
           70      10000
           80     304500
           90      58000
          100      51600
          110      20300
                    7000

-- Group By Single Column ROLLUP
-- ==============================
DEPARTMENT_ID SALARY_SUM
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     156400
           60      28800
           70      10000
           80     304500
           90      58000
          100      51600
          110      20300
                    7000
                  691400

-- Group By Single Column CUBE
-- ============================
DEPARTMENT_ID SALARY_SUM
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     156400
           60      28800
           70      10000
           80     304500
           90      58000
          100      51600
          110      20300
                    7000
                  691400

Group By Two Columns ROLLUP and CUBE

-- General Group By Two Columns Query
-- ===================================
SELECT department_id, job_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;

-- Group By Two Column ROLLUP
-- ===========================
SELECT department_id, job_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY ROLLUP(department_id, job_id)
ORDER BY department_id, job_id;

-- Group By Two Column CUBE
-- =========================
SELECT department_id, 
       job_id, 
       SUM(salary) AS salary_sum
  FROM employees
 GROUP BY CUBE(department_id, job_id)
 ORDER BY department_id, job_id;

Output

-- General Group By Two Columns Query
-- ===================================
DEPARTMENT_ID JOB_ID     SALARY_SUM
------------- ---------- ----------
           10 AD_ASST          4400
           20 MK_MAN          13000
           20 MK_REP           6000
           30 PU_CLERK        13900
           30 PU_MAN          11000
           40 HR_REP           6500
           50 SH_CLERK        64300
           50 ST_CLERK        55700
           50 ST_MAN          36400
           60 IT_PROG         28800
           70 PR_REP          10000
           80 SA_MAN          61000
           80 SA_REP         243500
           90 AD_PRES         24000
           90 AD_VP           34000
          100 FI_ACCOUNT      39600
          100 FI_MGR          12000
          110 AC_ACCOUNT       8300
          110 AC_MGR          12000
              SA_REP           7000

-- Group By Two Column ROLLUP
-- =================================
DEPARTMENT_ID JOB_ID     SALARY_SUM
------------- ---------- ----------
           10 AD_ASST          4400
           10                  4400
           20 MK_MAN          13000
           20 MK_REP           6000
           20                 19000
           30 PU_CLERK        13900
           30 PU_MAN          11000
           30                 24900
           40 HR_REP           6500
           40                  6500
           50 SH_CLERK        64300
           50 ST_CLERK        55700
           50 ST_MAN          36400
           50                156400
           60 IT_PROG         28800
           60                 28800
           70 PR_REP          10000
           70                 10000
           80 SA_MAN          61000
           80 SA_REP         243500
           80                304500
           90 AD_PRES         24000
           90 AD_VP           34000
           90                 58000
          100 FI_ACCOUNT      39600
          100 FI_MGR          12000
          100                 51600
          110 AC_ACCOUNT       8300
          110 AC_MGR          12000
          110                 20300
              SA_REP           7000
                               7000
                             691400

-- Group By Two Column CUBE
-- ==================================
DEPARTMENT_ID JOB_ID     SALARY_SUM
------------- ---------- ----------
           10 AD_ASST          4400
           10                  4400
           20 MK_MAN          13000
           20 MK_REP           6000
           20                 19000
           30 PU_CLERK        13900
           30 PU_MAN          11000
           30                 24900
           40 HR_REP           6500
           40                  6500
           50 SH_CLERK        64300
           50 ST_CLERK        55700
           50 ST_MAN          36400
           50                156400
           60 IT_PROG         28800
           60                 28800
           70 PR_REP          10000
           70                 10000
           80 SA_MAN          61000
           80 SA_REP         243500
           80                304500
           90 AD_PRES         24000
           90 AD_VP           34000
           90                 58000
          100 FI_ACCOUNT      39600
          100 FI_MGR          12000
          100                 51600
          110 AC_ACCOUNT       8300
          110 AC_MGR          12000
          110                 20300
              AC_ACCOUNT       8300
              AC_MGR          12000
              AD_ASST          4400
              AD_PRES         24000
              AD_VP           34000
              FI_ACCOUNT      39600
              FI_MGR          12000
              HR_REP           6500
              IT_PROG         28800
              MK_MAN          13000
              MK_REP           6000
              PR_REP          10000
              PU_CLERK        13900
              PU_MAN          11000
              SA_MAN          61000
              SA_REP           7000
              SA_REP         250500
              SH_CLERK        64300
              ST_CLERK        55700
              ST_MAN          36400
                               7000
                             691400

Grouping Along With ROLLUP and CUBE

-- Grouping and Group By Two Column CUBE
-- ====================================== 
SELECT department_id, 
       job_id, 
       SUM(salary) AS salary_sum, 
       grouping_id(department_id, job_id) AS sub_total
  FROM employees
 GROUP BY CUBE(department_id, job_id)
 ORDER BY department_id, job_id;

-- Grouping and Group By Two Column CUBE (With NVL and Case Usage)
-- ====================================== 
SELECT NVL(department_id, 999), 
       NVL(job_id, 'All Jobs'), 
       SUM(salary) AS salary_sum, 
       CASE grouping_id(department_id, job_id)
       WHEN 0 THEN 'Job Sum'
       WHEN 1 THEN 'Dept Sub-Total'
       WHEN 2 THEN 'Job Sub-Total'
       WHEN 3 THEN 'Grand Total'
       END AS sub_total
  FROM employees
 GROUP BY CUBE(department_id, job_id)
 ORDER BY department_id, job_id;

Output

-- Grouping and Group By Two Column CUBE
-- ============================================
DEPARTMENT_ID JOB_ID     SALARY_SUM  SUB_TOTAL
------------- ---------- ---------- ----------
           10 AD_ASST          4400          0
           10                  4400          1
           20 MK_MAN          13000          0
           20 MK_REP           6000          0
           20                 19000          1
           30 PU_CLERK        13900          0
           30 PU_MAN          11000          0
           30                 24900          1
           40 HR_REP           6500          0
           40                  6500          1
           50 SH_CLERK        64300          0
           50 ST_CLERK        55700          0
           50 ST_MAN          36400          0
           50                156400          1
           60 IT_PROG         28800          0
           60                 28800          1
           70 PR_REP          10000          0
           70                 10000          1
           80 SA_MAN          61000          0
           80 SA_REP         243500          0
           80                304500          1
           90 AD_PRES         24000          0
           90 AD_VP           34000          0
           90                 58000          1
          100 FI_ACCOUNT      39600          0
          100 FI_MGR          12000          0
          100                 51600          1
          110 AC_ACCOUNT       8300          0
          110 AC_MGR          12000          0
          110                 20300          1
              AC_ACCOUNT       8300          2
              AC_MGR          12000          2
              AD_ASST          4400          2
              AD_PRES         24000          2
              AD_VP           34000          2
              FI_ACCOUNT      39600          2
              FI_MGR          12000          2
              HR_REP           6500          2
              IT_PROG         28800          2
              MK_MAN          13000          2
              MK_REP           6000          2
              PR_REP          10000          2
              PU_CLERK        13900          2
              PU_MAN          11000          2
              SA_MAN          61000          2
              SA_REP           7000          0
              SA_REP         250500          2
              SH_CLERK        64300          2
              ST_CLERK        55700          2
              ST_MAN          36400          2
                               7000          1
                             691400          3

-- Grouping and Group By Two Column CUBE (With NVL and Case Usage)
-- =========================================================
NVL(DEPARTMENT_ID,999) NVL(JOB_ID SALARY_SUM SUB_TOTAL
---------------------- ---------- ---------- --------------
                    10 AD_ASST          4400 Job Sum
                    10 All Jobs         4400 Dept Sub-Total
                    20 MK_MAN          13000 Job Sum
                    20 MK_REP           6000 Job Sum
                    20 All Jobs        19000 Dept Sub-Total
                    30 PU_CLERK        13900 Job Sum
                    30 PU_MAN          11000 Job Sum
                    30 All Jobs        24900 Dept Sub-Total
                    40 HR_REP           6500 Job Sum
                    40 All Jobs         6500 Dept Sub-Total
                    50 SH_CLERK        64300 Job Sum
                    50 ST_CLERK        55700 Job Sum
                    50 ST_MAN          36400 Job Sum
                    50 All Jobs       156400 Dept Sub-Total
                    60 IT_PROG         28800 Job Sum
                    60 All Jobs        28800 Dept Sub-Total
                    70 PR_REP          10000 Job Sum
                    70 All Jobs        10000 Dept Sub-Total
                    80 SA_MAN          61000 Job Sum
                    80 SA_REP         243500 Job Sum
                    80 All Jobs       304500 Dept Sub-Total
                    90 AD_PRES         24000 Job Sum
                    90 AD_VP           34000 Job Sum
                    90 All Jobs        58000 Dept Sub-Total
                   100 FI_ACCOUNT      39600 Job Sum
                   100 FI_MGR          12000 Job Sum
                   100 All Jobs        51600 Dept Sub-Total
                   110 AC_ACCOUNT       8300 Job Sum
                   110 AC_MGR          12000 Job Sum
                   110 All Jobs        20300 Dept Sub-Total
                   999 AC_ACCOUNT       8300 Job Sub-Total
                   999 AC_MGR          12000 Job Sub-Total
                   999 AD_ASST          4400 Job Sub-Total
                   999 AD_PRES         24000 Job Sub-Total
                   999 AD_VP           34000 Job Sub-Total
                   999 FI_ACCOUNT      39600 Job Sub-Total
                   999 FI_MGR          12000 Job Sub-Total
                   999 HR_REP           6500 Job Sub-Total
                   999 IT_PROG         28800 Job Sub-Total
                   999 MK_MAN          13000 Job Sub-Total
                   999 MK_REP           6000 Job Sub-Total
                   999 PR_REP          10000 Job Sub-Total
                   999 PU_CLERK        13900 Job Sub-Total
                   999 PU_MAN          11000 Job Sub-Total
                   999 SA_MAN          61000 Job Sub-Total
                   999 SA_REP           7000 Job Sum
                   999 SA_REP         250500 Job Sub-Total
                   999 SH_CLERK        64300 Job Sub-Total
                   999 ST_CLERK        55700 Job Sub-Total
                   999 ST_MAN          36400 Job Sub-Total
                   999 All Jobs         7000 Dept Sub-Total
                   999 All Jobs       691400 Grand Total

Reference

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s