Oracle DBA

When you prepare reports, you often need numbers at different levels: detailed rows, subtotals, and a grand total. Without GROUPING SETS, you would write multiple queries and stitch them together with UNION ALL. With GROUPING SETS, one query does it all — more readable, more efficient.

Let’s explore this using the HR schema tables (employees, departments, jobs, job_history).

Example 1: Salaries by Department, Job, and Totals

This query calculates salaries at four levels:

• By department and job (detail level)
• By department only
• By job only
• The grand total

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
    (department_id, job_id),
    (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
              DEV                18000
              HR                  8500
                                29500

Here you can see detail rows (10/DEV = 11000), subtotals per department (Dept 10 = 15000), subtotals per job (DEV = 18000), and the grand total (29500).

Example 2: Counting Employees by Manager and Department

This query counts employees:

• By manager and department
• By manager only
• By department only
• Overall headcount

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

Output:

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

For manager 100, we see 3 employees in department 10, and the subtotal (3). Department totals (10 = 3, 20 = 2) and the grand total (6) are also included.

Example 3: Average Salary by Location, Department, and Job

Here we enrich the data with department locations. The query computes average salary:

• By location, department, and job
• By department only
• By job only
• Grand average

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

Output:

LOCATION_ID DEPARTMENT_ID JOB_ID     AVG_SALARY
----------- ------------- ---------- ----------
       1000            10 DEV              5500
       1000            10 HR               4000
       2000            20 DEV              7000
       2000            20 HR               4500
                      10                   5000
                      20                   5750
                                ADM       3000
                                DEV       6000
                                HR        4250
                                          4916.67

You get averages at the most detailed level (location + dept + job), at department level (10 = 5000), at job level (DEV = 6000), and the overall average (≈4916.67).

Example 4: Total and Average Salary Together

This query shows how to calculate both total and average salary in one result set:

• By department and job
• By department
• By job
• Grand total

SELECT e.department_id, e.job_id, 
       SUM(e.salary) AS total_salary, 
       AVG(e.salary) AS avg_salary
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
GROUP BY GROUPING SETS (
    (e.department_id, e.job_id),
    (e.department_id),
    (e.job_id),
    ()
);

Output:

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

With GROUPING SETS, you can display both measures side by side — very useful in payroll or finance reports.

Example 5: Counting Job Changes from Job History

This query counts how many times employees have changed jobs:

• By employee and department
• By employee
• By department
• Overall 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 GROUPING SETS (
    (e.employee_id, j.department_id),
    (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

For employee 1, we see two job changes (10 and 20). Department totals and the overall count (8) are also included.

Final Thoughts

• GROUPING SETS gives you detail + subtotals + grand totals in one query.
• It’s cleaner and more efficient than UNION ALL.
• Combine with GROUPING() or GROUPING_ID() to clearly label subtotal rows.

Whenever your report requires multiple levels of aggregation, GROUPING SETS is the right tool.