GROUPING

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set.

Syntax

grouping(<group_by_expression> [, ...])

Arguments

Required

<group_by_expression>: One or more expressions that must exactly match expressions in the GROUP BY clause.

Returns

Returns an integer bit mask. Each bit corresponds to an argument, with the rightmost argument as the least-significant bit. A bit is 0 if the expression is in the grouping set; 1 if it is not.

Considerations

Used with grouping sets (ROLLUP, CUBE, GROUPING SETS) to identify which columns were aggregated.
Maximum of 31 expressions can be used as arguments.
Arguments are not evaluated but must exactly match GROUP BY expressions.
Helps distinguish between actual NULL values and NULLs representing aggregated rows.

Examples

Basic GROUPING with ROLLUP

Identify aggregation levels with ROLLUP.

SELECT make, model, grouping(make, model), sum(sales)
FROM items_sold
GROUP BY ROLLUP(make, model);

Returns:

make	model	grouping	sum
Foo	GT	0	10
Foo	Tour	0	20
Bar	City	0	15
Bar	Sport	0	5
Foo	NULL	1	30
Bar	NULL	1	20
NULL	NULL	3	50

0: Both make and model are grouped.
1: model is aggregated (not grouped).
3: Both make and model are aggregated.

Filter Subtotals

Use GROUPING to filter specific aggregation levels.

SELECT make, model, sum(sales)
FROM items_sold
GROUP BY ROLLUP(make, model)
HAVING grouping(make, model) = 1;

Returns only the subtotals by make (where model is aggregated).