Understand GROUPING Functions with ROLLUP and CUBE in Hologres - Hologres

Hologres is compatible with PostgreSQL and supports standard PostgreSQL syntax for data development. The functions supported are a subset of PostgreSQL functions. For the full list, see Aggregate Functions in the PostgreSQL 11 documentation.

GROUPING

GROUPING works alongside GROUP BY extensions such as GROUPING SETS, ROLLUP, and CUBE. When these extensions produce subtotal rows, they fill grouping columns with NULL. If the underlying data also contains NULL values, you cannot distinguish a real NULL from a subtotal NULL by inspecting column values alone. GROUPING lets you tell them apart.

Syntax

GROUPING( <expr1> [, <expr2>, ...] )

Parameters

Parameter	Description
`expr`	A column in the `GROUP BY` clause

Return values

Single column: GROUPING(expr) returns an integer.

Return value	Meaning
`0`	The row is grouped by `expr`
`1`	The row is not grouped by `expr` (subtotal row)

Multiple columns: GROUPING(expr1, expr2, ..., exprN) returns an integer of a bit vector containing GROUPING(expr1), GROUPING(expr2), ..., GROUPING(exprN).

Example

The following example creates a table and inserts data, then uses GROUPING with GROUPING SETS to produce subtotals at multiple levels.

Create the table and insert data.

CREATE TABLE t1 (
  col_x INT,
  col_y INT,
  col_z INT
);

INSERT INTO t1 VALUES(1, 2, 1), (1, 2, 3);
INSERT INTO t1 VALUES(2, 1, 10), (2, 2, 11), (2, 2, 3);

SELECT * FROM t1 ORDER BY col_x, col_y, col_z;

Result:

 col_x | col_y | col_z
-------+-------+-------
      1 |     2 |     1
      1 |     2 |     3
      2 |     1 |    10
      2 |     2 |     3
      2 |     2 |    11
(5 rows)

Query with GROUPING.
```
SELECT
  col_x,
  col_y,
  SUM(col_z),
  GROUPING(col_x),
  GROUPING(col_y),
  GROUPING(col_x, col_y)
FROM t1
GROUP BY GROUPING SETS ((col_x), (col_y), ())
ORDER BY 1, 2;
```
Result:
```
 col_x | col_y | sum | grouping | grouping | grouping
-------+-------+-----+----------+----------+----------
      1 |       |   4 |        0 |        1 |        1
      2 |       |  24 |        0 |        1 |        1
        |     1 |  10 |        1 |        0 |        2
        |     2 |  18 |        1 |        0 |        2
        |       |  28 |        1 |        1 |        3
(5 rows)
```
In the result, 3 is an integer to which binary number 11 is converted. Each row in the result maps to a grouping level:
- Rows where col_x = 1 or col_x = 2: grouped by col_x, not by col_y — GROUPING(col_x) = 0, GROUPING(col_y) = 1, GROUPING(col_x, col_y) = 1 (binary 01).
- Rows where col_y = 1 or col_y = 2: grouped by col_y, not by col_x — GROUPING(col_x) = 1, GROUPING(col_y) = 0, GROUPING(col_x, col_y) = 2 (binary 10).
- The grand total row (both columns NULL): not grouped by either column — GROUPING(col_x) = 1, GROUPING(col_y) = 1, GROUPING(col_x, col_y) = 3 (binary 11).