Oracle Reporting 3 - Aggregation Level

To determine the aggregation level in a report, Oracle provides grouping_id and group_id functions.

Grouping_ID:GROUPING_ID returns a single number that enables you to determine the exact GROUP BY level. For each row, GROUPING_ID takes the set of 1's and 0's that would be generated if you used the appropriate GROUPING functions and concatenates them, forming a bit vector. The bit vector is treated as a binary number, and the number's base-10 value is returned by the GROUPING_ID function.

select deptno, job, sum(sal), grouping_id(deptno, job)
from emp
group by cube(deptno, job)
order by deptno nulls last; 

    DEPTNO JOB         SUM(SAL) GROUPING_ID(DEPTNO,JOB)
---------- --------- ---------- -----------------------
        10 CLERK           1300                       0
        10 MANAGER         2450                       0
        10 PRESIDENT      14999                       0
        10                18749                       1
        20 ANALYST         6000                       0
        20 CLERK           1900                       0
        20 MANAGER         2975                       0
        20                10875                       1
        30 CLERK            950                       0
        30 MANAGER         2850                       0
        30 SALESMAN        5600                       0
        30                 9400                       1
           ANALYST         6000                       2
           CLERK           4150                       2
           MANAGER         8275                       2
           PRESIDENT      14999                       2
           SALESMAN        5600                       2
                          39024                       3

Group_ID: The GROUP_ID function lets you distinguish among duplicate groupings. If there are multiple sets of rows calculated for a given level, GROUP_ID assigns the value of 0 to all the rows in the first set. All other sets of duplicate rows for a particular grouping are assigned higher values, starting with 1.

SELECT country_iso_code, SUBSTR(cust_state_province,1,12), SUM(amount_sold),
GROUPING_ID(country_iso_code, cust_state_province) GROUPING_ID, GROUP_ID()
FROM sales, customers, times, countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id
AND customers.country_id=countries.country_id AND times.time_id= '30-OCT-00'
AND country_iso_code IN ('FR', 'ES')
GROUP BY GROUPING SETS (country_iso_code,
ROLLUP(country_iso_code, cust_state_province));

CO SUBSTR(CUST_STATE_PROVINCE,1,1                   SUM(AMOUNT_SOLD) GROUPING_ID GROUP_ID()
-- ------------------------------------------------ ---------------- ----------- ----------
ES Alicante                                                   135.32           0          0
ES Valencia                                                  4133.56           0          0
ES Barcelona                                                   24.22           0          0
FR Centre                                                       74.3           0          0
FR Aquitaine                                                  231.97           0          0
FR Rhtne-Alpes                                               1624.69           0          0
FR Ile-de-Franc                                              1860.59           0          0
FR Languedoc-Ro                                               4287.4           0          0
                                                            12372.05           3          0
ES                                                            4293.1           1          0
FR                                                           8078.95           1          0
ES                                                            4293.1           1          1
FR                                                           8078.95           1          1

Groupings: (country_id, cust_state_province), (country_id), (country_id), and ().

You can filter out duplicate (region) groupings from the previous example by adding a HAVING clause condition GROUP_ID()=0 to the query.