Group By Grouping Sets (Teradata Database) - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Teradata Warehouse Miner
Release Number
November 2018
English (United States)
Last Update
Product Category

If the Group By Style on the analysis parameters tab is set to User specified, a Group By Grouping Sets element may be dragged onto the empty node under the Group By Clause, giving the following result. If User specified is not checked, a warning is given when the Group By Grouping Sets element is applied that the option will be automatically checked.

Only a Grouping Set SQL element or another Group By Grouping Sets element (thereby nesting grouping sets) may be moved into the Grouping Sets folder. Under a Grouping Set element, one or more columns can be moved into the folder titled Expressions (but not expressions or column positions), leading to a comma separated list of the columns that define a single dimension rollup or a multiple dimension cube. If the Expressions folder is left empty, an empty grouping set ( ) corresponding to a grand total is created.

In the following example with the tutorial data table twm_customer, a Group By Grouping Sets of customer_count over gender and marital_ status and over state_code is requested, conceptually ((gender, marital_status), (state_code), ()). Note that a Where Clause limiting rows to customer records for states with name beginning with ‘N’ is specified in order to reduce the output in this example.

Selected variables and expert options are shown below.
Variable Creation > Input > Variables

Variable Creation > Input > expert options > Group By Clause > Group by Grouping Sets

The resulting data is listed in the following table.

Group By Grouping Sets: Resulting Data
gender marital_status state_code customer_count
    NB 3
    NC 9
    NJ 12
    NM 7
    NV 4
    NY 107
F 1   28
F 2   37
F 3   4
F 4   7
M 1   25
M 2   35
M 3   1
M 4   5

The blank spaces in the answer table above represent null values that signify the empty set rather than information that is missing. Through this technique, aggregations at many different levels are shown in a single answer table.

There are no special properties for the Group By Grouping Sets element.