Purpose
GROUPING_ID() calculates the combined value of a set of expressions and returns a number representing the level of the row within the GROUP BY hierarchy.
The GROUPING_ID() function creates a bit vector by concatenating the sequences of 1s and 0s produced when the GROUPING() function is applied to each attribute. This bit vector is then treated as a binary number, and the GROUPING_ID function subsequently returns its corresponding decimal value.
The following table lists the possible GROUPING_ID values when CUBE(a, b) is used as the grouping expression.
| Aggregation level | Bit vector | GROUPING_ID |
|---|---|---|
| a, b | 00 | 0 |
| a | 01 | 1 |
| b | 10 | 2 |
| Total | 11 | 3 |
Syntax
GROUPING_ID(attributeList)
Parameters
| Parameter | Description |
|---|---|
attributeList |
A list of expressions for which the combined value is to be calculated. |
Examples
Create a table named orders to store data, including the order ID (order_id), year (year), quarter (quarter), and order amount (amount), for various orders.
obclient [SYS]> CREATE TABLE orders (
-> order_id INT,
-> year INT,
-> quarter INT,
-> amount DECIMAL(10, 2)
-> );
Insert sample data and use GROUPING_ID() to analyze the data.
obclient [SYS]> INSERT INTO orders (order_id, year, quarter, amount) VALUES
-> (1, 2021, 1, 1000.00),
-> (2, 2021, 1, 1500.00),
-> (3, 2021, 2, 2000.00),
-> (4, 2021, 2, 2500.00),
-> (5, 2021, 3, 3000.00),
-> (6, 2021, 4, 3500.00),
-> (7, 2021, 4, 4000.00),
-> (8, 2022, 1, 4500.00),
-> (9, 2022, 2, 5000.00),
-> (10, 2022, 3, 5500.00),
-> (11, 2022, 4, 6000.00);
The result set generated by this query will display all possible aggregate combinations of different years and quarters, along with the total order amount for each combination. GROUPING_ID provides a unique number for each combination to indicate the aggregation level.
In this example, GROUPING_ID(year, quarter) will produce the following values:
- When both
yearandquarterare not NULLs,GROUPING_ID()returns0. - When only
yearis not NULL,GROUPING_ID()returns1. - When both
yearandquarterare NULLs,GROUPING_ID()returns3.
obclient [SYS]> SELECT
-> year,
-> quarter,
-> GROUPING_ID(year, quarter) AS grouping_level,
-> SUM(amount) AS total_amount
-> FROM orders
-> GROUP BY ROLLUP(year, quarter)
-> ORDER BY year, quarter;
The return result is as follows:
+------+---------+----------------+--------------+
| YEAR | QUARTER | GROUPING_LEVEL | TOTAL_AMOUNT |
+------+---------+----------------+--------------+
| 2021 | 1 | 0 | 2500 |
| 2021 | 2 | 0 | 4500 |
| 2021 | 3 | 0 | 3000 |
| 2021 | 4 | 0 | 7500 |
| 2021 | NULL | 1 | 17500 |
| 2022 | 1 | 0 | 4500 |
| 2022 | 2 | 0 | 5000 |
| 2022 | 3 | 0 | 5500 |
| 2022 | 4 | 0 | 6000 |
| 2022 | NULL | 1 | 21000 |
| NULL | NULL | 3 | 38500 |
+------+---------+----------------+--------------+
11 rows in set