Oracle DBA

When working with ROLLUP or CUBE, you’ll notice NULL values appear in subtotals and grand totals.
But how do you tell the difference between a real NULL value in the data and a NULL that just means “subtotal here”?

That’s where GROUPING_ID comes in. It returns a numeric code indicating which columns are aggregated (rolled up).

Example 1: Distinguishing Department and Job Aggregations in Salary Totals

We calculate total salaries grouped by department and job. Using GROUPING_ID, we mark which rows are:

• Detail rows
• Department totals
• Job totals
• Grand total

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

Output:

DEPARTMENT_ID JOB_ID     TOTAL_SALARY GROUPING_LEVEL
------------- ---------- ------------ --------------
           10 DEV                11000              0
           10 HR                  4000              0
           10                    15000              1
           20 DEV                 7000              0
           20 HR                  4500              0
           20                    11500              1
              ADM                 3000              2
                                 29500              3

GROUPING_LEVEL values:

• 0 → Detail row (both dept and job present)
• 1 → Department subtotal
• 2 → Job subtotal
• 3 → Grand total

Example 2: Identifying Aggregation Levels in Employee Count by Manager and Department

Here we count employees by manager and department, with ROLLUP.

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

Output:

MANAGER_ID DEPARTMENT_ID EMPLOYEE_COUNT GROUPING_LEVEL
---------- ------------- -------------- --------------
       100            10              3              0
       100                            3              1
       101            20              2              0
       101                            2              1
                      10              3              2
                      20              2              2
                                      5              3

The codes show clearly whether the row is detail (0), subtotal by manager (1), subtotal by department (2), or grand total (3).

Example 3: Summing and Averaging Salaries by Department, Job, and Location Using GROUPING_ID

With CUBE, we calculate salary totals and averages by location, department, and job. GROUPING_ID tells us which level of aggregation each row represents.

SELECT d.location_id, e.department_id, e.job_id,
       SUM(e.salary) AS total_salary, AVG(e.salary) AS avg_salary,
       GROUPING_ID(d.location_id, e.department_id, e.job_id) AS grouping_level
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 GROUPING_LEVEL
----------- ------------- ---------- ------------ ---------- --------------
       1000            10 DEV                11000       5500              0
       1000            10 HR                  4000       4000              0
       1000            10                    15000       5000              1
       2000            20 DEV                 7000       7000              0
       2000            20 HR                  4500       4500              0
       2000            20                    11500       5750              1
       1000                                  15000       5000              3
                          DEV                18000       6000              6
                                             29500       4916.67           7

GROUPING_ID helps identify whether the row is full detail (0), department subtotal (1), location subtotal (3), job subtotal (6), or grand total (7).

Example 4: Analyzing Job History by Aggregation Levels

Now let’s check job changes by employee and department, with CUBE.

SELECT e.employee_id, j.department_id, COUNT(*) AS job_changes,
       GROUPING_ID(e.employee_id, j.department_id) AS grouping_level
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 GROUPING_LEVEL
----------- ------------- ----------- --------------
          1            10           1              0
          1            20           1              0
          1                         2              1
          2            10           1              0
          2                         1              1
          3            10           1              0
          3            20           1              0
          3                         2              1
          4            20           2              0
          4                         2              1
          6            10           1              0
          6                         1              1
                       10           4              2
                       20           4              2
                                    8              3

With GROUPING_ID, it’s clear which rows are employee-level totals, department totals, or overall totals.

Example 5: Salary Analysis by Job and Department Using GROUPING_ID

Finally, we compute both average and total salary with department/job roll-ups.

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

Output:

JOB_ID     DEPARTMENT_ID AVG_SALARY TOTAL_SALARY GROUPING_LEVEL
---------- ------------- ---------- ------------ --------------
DEV                   10       5500        11000              0
DEV                   20       7000         7000              0
DEV                            6000        18000              1
HR                    10       4000         4000              0
HR                    20       4500         4500              0
HR                             4250         8500              1
ADM                            3000         3000              1
                               4916.67     29500              3

Now you can distinguish between job/department detail (0), job totals (1), and the grand total (3).

Key Takeaways

• GROUPING_ID provides a simple numeric way to detect subtotals vs detail rows.
• Without it, NULL values can be confusing in roll-up/cube queries.
• It’s perfect for reporting and BI dashboards, where you need to label rows as “Detail”, “Subtotal”, or “Grand Total”.

With GROUPING_ID, your reports become not just numbers, but structured insights — clearly showing where detail ends and totals begin.