GROUPING

​

Syntax

​

Aggregate

grouping(expression[, additional_expression, ...])

• Use in an aggregate query, with one through 31 arguments present in its GROUP BY.

​

Grouping id form (aggregate)

grouping_id(expression[, additional_expression, ...])

• Use in an aggregate query, with one through 31 arguments present in its GROUP BY.

​

Arguments

​

Aggregate, grouping id form

​

Returns

• Type: BIGINT or INTEGER, depending on the query; query tools report BIGINT
• Possible values: An integer bitmask. Each argument contributes 0 when present in the grouping set and 1 when omitted; the rightmost argument is the least significant bit. Ordinary GROUP BY returns zero.
• NULL behavior: A NULL data value in a grouping expression still counts as present and contributes zero. A one bit marks an expression omitted by the grouping set, independently of its data value.
• Empty input: The flag is computed for every group produced by the enclosing aggregate, including a total grouping set if that aggregate produces one. It does not itself create a row on empty input.

​

Properties

​

Usage notes

• GROUPING_ID is an alias of GROUPING and uses the same argument order for its result bits.
• Deterministic: The current grouping set and argument order determine the bitmask.
• DISTINCT is not accepted.
• GROUPING is used with grouped queries. It is not available as a window function.
• Exact result: The supported argument count fits the signed 32-bit mask calculation.

​

Examples

​

Ordinary groups retain their grouping expression

SELECT "value", CAST(grouping("value") AS BIGINT) AS grouping_flag FROM (VALUES (1),(2)) AS t("value") GROUP BY "value" ORDER BY "value";

​

Alias calling form: GROUPING_ID

SELECT "value", CAST(grouping_id("value") AS BIGINT) AS grouping_flag FROM (VALUES (1),(2)) AS t("value") GROUP BY "value" ORDER BY "value";

​

Limitations

• Supply 1 through 31 arguments. Calls with 32 or more arguments are rejected.
• Query tools report BIGINT, but some grouping-set queries return INTEGER. Cast the result to BIGINT when you need a consistent type.
• Use columns from the GROUP BY clause as arguments. Queries with more than 64 grouping columns can be rejected. Repeated grouping sets can also exceed the 64-position limit used to track grouping columns.
• DISTINCT, FILTER and window calling forms are unsupported.
• A GROUPING result can fail with a type mismatch. Cast it to BIGINT as shown in the example.