GROUPING_ID

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 1 and 0 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.

For example, when you use CUBE(a, b) as the grouping expression, the possible GROUPING_ID values are as follows:

Considerations

• When an expression is present in the grouping set, the value returned by GROUPING_ID will increase; if it is not present, it will remain unchanged.

Syntax

GROUPING_ID(attributeList)

Parameters

Examples

Create a table named orders to store data for various orders, including order ID (order_id), year (year), quarter (quarter), and order amount (amount).

obclient [SYS]> CREATE TABLE orders (
    ->     order_id INT,
    ->     year INT,
    ->     quarter INT,
    ->     amount DECIMAL(10, 2)
    -> );

Next, insert some sample data and use GROUPING_ID to analyze these 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 year and quarter are non-NULL (most specific grouping), the value of GROUPING_ID is 0.
• When only year is non-NULL (grouping by year), the value of GROUPING_ID is 1.
• When both year and quarter are NULL (total order amount), the value of GROUPING_ID is 2.

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 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