GROUPING SETS Option - Teradata Database

SQL Data Manipulation Language
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Analyzes data in one of two possible ways:

  • In a single dimension without performing a full ROLLUP.
  • In multiple dimensions without performing a full CUBE.

    • where:

     

    Syntax element …

    Specifies …

    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.

    You cannot reference columns that have a BLOB or CLOB data type unless they are first CAST to another type or passed to a function whose result is not a LOB. For example, casting a BLOB to a BYTE or VARBYTE type, or casting a CLOB to a CHARACTER or VARCHAR type.

    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.

    The term grand total here refers to a summation of all the individual group totals, not a summation 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 Grouping Set Option” on page 142 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 Grouping Set Option” on page 136 for more information.

    grouping_sets_
    specification

    any of the following:

  • a parenthetically enclosed ordinary_grouping_set
  • a rollup_list
  • a cube_list
  • an empty_grouping_set
  • a 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.

    GROUPING SETS is ANSI SQL:2011 compliant.

    This rule applies to the use of the GROUPING SETS option:

  • You cannot specify a SAMPLE clause in a query that also specifies the GROUPING SETS option in a GROUP BY clause. The exception is a query where the GROUPING SETS option is specified in a derived table or a view, and the SAMPLE clause is specified in the outer query.

    • GROUPING SETS allows you to group your results set multiple times. Empty parentheses () specify a single grand total group that sums all the groups specified in the query.

    The following example query forms the first group by state, county then forms the second group by city then forms the third group consisting of the whole table.

    Suppose you have the following sales_view table data:

     

    You are interested in looking at sales summaries by county within state, by city, and by state.

    The following SELECT statement analyzes the data in sales_view for this information: 

         SELECT state, county, city, SUM(margin) 
         FROM sales_view
         GROUP BY GROUPING SETS ((state,county),(city),());

    The query reports the following results, where the QUESTION MARK character indicates a null:

     

    Notice that nulls are used to represent the empty set in this answer set, not missing information. The nulls mean that information is not reported at the grouping level represented, not that the information is missing from the sales_view base table. This usage is similar to the way nulls are used in outer joins to indicate empty sets of nonmatching rows.