Skip to main content

GROUPING

Description​

Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 if the column is aggregated (i.e., coming from a summary row generated by ROLLUP, CUBE, or GROUPING SETS) and 0 if it is not aggregated. This function is useful for distinguishing the NULL values produced by these operations from actual NULL values in the data.

Syntax​

GROUPING( <column_expression> )

Parameters​

ParameterDescription
<column_expression>A column or an expression that appears in the GROUP BY clause.

Return Value​

Returns a BIGINT value. The function returns 1 if the specified column expression is aggregated and 0 if it is not.

Examples​

The following example groups the camp column and aggregates the count of occupation. The GROUPING function is applied to the camp column to differentiate between the summary row added by the ROLLUP operation and actual NULL values in the data.

CREATE TABLE `roles` (
role_id INT,
occupation VARCHAR(32),
camp VARCHAR(32),
register_time DATE
)
UNIQUE KEY(role_id)
DISTRIBUTED BY HASH(role_id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

INSERT INTO `roles` VALUES
(0, 'who am I', NULL, NULL),
(1, 'mage', 'alliance', '2018-12-03 16:11:28'),
(2, 'paladin', 'alliance', '2018-11-30 16:11:28'),
(3, 'rogue', 'horde', '2018-12-01 16:11:28'),
(4, 'priest', 'alliance', '2018-12-02 16:11:28'),
(5, 'shaman', 'horde', NULL),
(6, 'warrior', 'alliance', NULL),
(7, 'warlock', 'horde', '2018-12-04 16:11:28'),
(8, 'hunter', 'horde', NULL);

SELECT
camp,
COUNT(occupation) AS occ_cnt,
GROUPING(camp) AS grouping
FROM
`roles`
GROUP BY
ROLLUP(camp);

The result set shows two NULL values under the camp column. The first NULL, with a grouping value of 1, is the summary row added by the ROLLUP operation representing the aggregate of all camp groups. The second NULL, with a grouping value of 0, represents an actual NULL value from the data.

+----------+---------+----------+
| camp | occ_cnt | grouping |
+----------+---------+----------+
| NULL | 9 | 1 |
| NULL | 1 | 0 |
| alliance | 4 | 0 |
| horde | 4 | 0 |
+----------+---------+----------+