GROUPING SETS Option - Teradata Database

Purpose

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.

Syntax

Syntax Elements

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” 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” for more information.

grouping_sets_ specification

Any of the following:

ordinary_grouping_setenclosed in parenthesis
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.

ANSI Compliance

GROUPING SETS is ANSI SQL:2011 compliant.

SAMPLE Clause and GROUPING SETS

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.

How GROUPING SETS Summarizes Data

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.

Example: GROUPING SETS

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:

sales_view
PID	cost	sale	margin	state	county	city
1	38350	50150	11800	CA	Los Angeles	Long Beach
1	63375	82875	19500	CA	San Diego	San Diego
1	46800	61200	14400	CA	Los Angeles	Avalon
1	40625	53125	12500	CA	Los Angeles	Long Beach

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:

state	county	city	SUM(margin)
-----	------	----	-----------
CA	Los Angeles	?	38700
CA	San Diego	?	19500
?	?	Long Beach	24300
?	?	San Diego	19500
?	?	Avalon	14400
?	?	?	58200

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.