Oracle DBA

SQL provides powerful extensions to GROUP BY: ROLLUP and CUBE.

• ROLLUP creates hierarchical totals (e.g., detail → subtotal → grand total).
• CUBE creates all possible combinations of aggregations.

These are extremely useful for building reports with subtotals and cross-tab summaries. Let’s see some practical examples.

Example 1: Total Salaries with ROLLUP by Department and Job

This query calculates total salaries:

• By department and job
• By department
• The grand total

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP (department_id, job_id);

Output:

DEPARTMENT_ID JOB_ID     TOTAL_SALARY
------------- ---------- ------------
           10 DEV                11000
           10 HR                  4000
           10                     15000
           20 DEV                 7000
           20 HR                  4500
           20                     11500
              ADM                 3000
                                29500

The hierarchy is clear: job totals roll up into department totals, which roll up into the grand total.

Example 2: Employee Count by Department and Manager Using ROLLUP

Here we count employees:

• By department and manager
• By department
• The overall total

SELECT department_id, manager_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY ROLLUP (department_id, manager_id);

Output:

DEPARTMENT_ID MANAGER_ID EMPLOYEE_COUNT
------------- ---------- --------------
           10        100              3
           10                         3
           20        101              2
           20                         2
                         5

Department 10 has 3 employees (all under manager 100), Department 20 has 2, and the grand total is 5.

Example 3: Total and Average Salary by Department, Job, and Location Using CUBE

Unlike ROLLUP, CUBE generates all combinations. Here we compute salary totals and averages:

• By location, department, and job (detail)
• By each subset (location + department, location + job, department + job, etc.)
• And the grand total

SELECT d.location_id, e.department_id, e.job_id, 
       SUM(e.salary) AS total_salary, 
       AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY CUBE (d.location_id, e.department_id, e.job_id);

Output (excerpt):

LOCATION_ID DEPARTMENT_ID JOB_ID     TOTAL_SALARY AVG_SALARY
----------- ------------- ---------- ------------ ----------
       1000            10 DEV                11000       5500
       1000            10 HR                  4000       4000
       1000            10                     15000       5000
       2000            20 DEV                 7000       7000
       2000            20 HR                  4500       4500
       2000            20                     11500       5750
       1000                                15000       5000
       2000                                11500       5750
                                DEV        18000       6000
                                HR          8500       4250
                                ADM         3000       3000
                                          29500       4916.67

This shows every possible combination — location totals, department totals, job totals, and the grand total.

Example 4: Job History Counts Using CUBE for Employee and Department

This query counts job changes from job_history:

• By employee and department
• By employee
• By department
• Grand total

SELECT e.employee_id, j.department_id, COUNT(*) AS job_changes
FROM job_history j
JOIN employees e ON j.employee_id = e.employee_id
GROUP BY CUBE (e.employee_id, j.department_id);

Output:

EMPLOYEE_ID DEPARTMENT_ID JOB_CHANGES
----------- ------------- -----------
          1            10           1
          1            20           1
          1                         2
          2            10           1
          2                         1
          3            10           1
          3            20           1
          3                         2
          4            20           2
          4                         2
          6            10           1
          6                         1
                       10           4
                       20           4
                                    8

Employee totals, department totals, and grand totals are all included.

Example 5: Employee Count and Average Salary by Department and Job Using ROLLUP

This query shows both count and average salary:

• By department and job
• By department
• Overall

SELECT department_id, job_id, 
       COUNT(employee_id) AS employee_count, 
       AVG(salary) AS avg_salary
FROM employees
GROUP BY ROLLUP (department_id, job_id);

Output:

DEPARTMENT_ID JOB_ID     EMPLOYEE_COUNT AVG_SALARY
------------- ---------- -------------- ----------
           10 DEV                     2       5500
           10 HR                      1       4000
           10                         3       5000
           20 DEV                     1       7000
           20 HR                      1       4500
           20                         2       5750
              ADM                     1       3000
                                      6       4916.67

This is a neat way to produce workforce statistics with roll-ups.

Example 6: Sales Totals by Product and Region Using CUBE

For a sales table with columns (region, product_category, sales_amount), we can calculate totals:

• By region and product
• By region only
• By product only
• Overall

SELECT region, product_category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY CUBE (region, product_category);

Output (example):

REGION      PRODUCT_CATEGORY TOTAL_SALES
----------- ---------------- -----------
East        Electronics           50000
East        Clothing              20000
East                             70000
West        Electronics           30000
West        Clothing              25000
West                             55000
            Electronics           80000
            Clothing              45000
                                 125000

Here we see totals for each region/product, region totals, product totals, and the grand total.

Key Differences: ROLLUP vs CUBE

• ROLLUP → Produces hierarchical totals (good for drilling down: dept → job → total).
• CUBE → Produces all combinations (good for cross-tab reporting: dept totals, job totals, combinations, overall).

Both simplify report queries significantly and eliminate the need for multiple UNION statements.

With ROLLUP and CUBE, you can generate detail, subtotals, and grand totals in a single elegant query — perfect for analytics and reporting.