Get Postgres Tips and Tricks
Subscribe to get advanced Postgres how-tos.
![]() |
![]() |
![]() |
2 SQL Tutorial : 2.2 Advanced Concepts : 2.2.6 Multidimensional Analysis : 2.2.6.5 GROUPING_ID Function
2.2.6.5 GROUPING_ID FunctionThe GROUPING_ID function provides a simplification of the GROUPING function in order to determine the subtotal level of a row in the result set from a ROLLBACK, CUBE, or GROUPING SETS extension.The GROUPING function takes only one column expression and returns an indication of whether or not a row is a subtotal over all values of the given column. Thus, multiple GROUPING functions may be required to interpret the level of subtotals for queries with multiple grouping columns.The GROUPING_ID function accepts one or more column expressions that have been used in the ROLLBACK, CUBE, or GROUPING SETS extensions and returns a single integer that can be used to determine over which of these columns a subtotal has been aggregated.The general syntax for use of the GROUPING_ID function is shown by the following.SELECT [ expr ...,][, expr ] ...{ ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr_1[, col_expr_2 ] [, ...] ) [, ...]The GROUPING_ID function takes one or more parameters that must be expressions of dimension columns specified in the expression list of a ROLLUP, CUBE, or GROUPING SETS extension of the GROUP BY clause.The GROUPING_ID function returns an integer value. This value corresponds to the base-10 interpretation of a bit vector consisting of the concatenated 1’s and 0’s that would be returned by a series of GROUPING functions specified in the same left-to-right order as the ordering of the parameters specified in the GROUPING_ID function.The following query shows how the returned values of the GROUPING_ID function represented in column gid correspond to the values returned by two GROUPING functions on columns loc and dname.In the following output, note the relationship between a bit vector consisting of the gf_loc value and gf_dname value compared to the integer given in gid.The following table provides specific examples of the GROUPING_ID function calculations based on the GROUPING function return values for four rows of the output.
The following table summarizes how the GROUPING_ID function return values correspond to the grouping columns over which aggregation occurs.
So to display only those subtotals by dname, the following simplified query can be used with a HAVING clause based on the GROUPING_ID function.
2 SQL Tutorial : 2.2 Advanced Concepts : 2.2.6 Multidimensional Analysis : 2.2.6.5 GROUPING_ID Function
![]() |
![]() |
![]() |