GROUPING SETS group_by_spec
Syntax Elements
- group_by_spec
{ ordinary_grouping_set |
empty_grouping_set |
rollup_list |
cube_list |
grouping_sets_specification
}
- ordinary_grouping_set
- One or more expressions used to group cubed report rows across multiple dimensions.
- All ordinary grouping sets must make explicit column references. You cannot specify column positions with GROUPING SETS.
- You cannot specify more than 190 columns in the ordinary grouping sets.
- To reference a column that has a LOB data type, do one of the following:
- Cast the LOB to a non-LOB type (for example, cast BLOB to BYTE or VARBYTE, cast CLOB to CHARACTER or VARCHAR)
- Pass the LOB to a function whose result is not a LOB.
- The expression cannot group result rows that have a BLOB, CLOB, ARRAY, or VARRAY type.
- empty_grouping_set
- A contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. This syntax is used to provide a grand total.
- Here, "grand total" is the sum of the individual group totals, not the sum of the nonaggregate data.
- rollup_list
- A standard grouping expression set specified with ROLLUP. If you specify more than one expression, the expression list must be enclosed by parentheses.
- The expression cannot group result rows that have a BLOB, CLOB, ARRAY, or VARRAY type.
- See ROLLUP Option for more information.
- cube_list
- a standard grouping expression set specified with CUBE. If you specify more than one expression, the expression list must be enclosed by parentheses.
- The expression cannot group result rows that have a BLOB, CLOB, ARRAY, or VARRAY type.
- See CUBE Option for more information.
- grouping_sets_specification
- Any of the following:
- (ordinary_grouping_set)
- rollup_list
- cube_list
- empty_grouping_set
- grouping_sets_specification
- There is no limit to the number of nesting levels for grouping sets specifications other than your system configuration.
- You cannot reference columns that have a LOB data type.