Using GROUPING_ID to Distinguish Aggregation Levels in SELECT Statement
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 subtotal2→ Job subtotal3→ 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_IDprovides a simple numeric way to detect subtotals vs detail rows.- Without it,
NULLvalues 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.
No Comments