ROLLUP Grouping Set Option - Teradata Database

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

ROLLUP Grouping Set Option

Purpose  

Analyzes a set of data across a single dimension with more than one level of detail.

Syntax  

where:

 

Syntax element …

Specifies …

ordinary_grouping
_set

one or more expressions used to group rolled up report rows across a single dimension.

The expression cannot group result rows that have a BLOB, CLOB, ARRAY, or VARRAY type.

All ordinary grouping sets must make explicit column references. You cannot specify column positions with ROLLUP.

Rolling up over a single column is equivalent to a simple GROUP BY on that same column.

You cannot specify more than 190 columns in the ordinary grouping sets.

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

ANSI Compliance

ROLLUP is ANSI SQL:2011 compliant.

Rules and Restrictions for ROLLUP

The rules are:

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

    • How ROLLUP Summarizes Data

    Given n column references, ROLLUP groups data at n levels of detail in one dimension. For example, in “Example 1: Basic ROLLUP” on page 161, the GROUP BY clause rolls up over three columns and reports three levels of control breaks.

    Example : Basic ROLLUP

    The following example returns the total margin in each county, city, state, and as a whole. This example shows how ROLLUP groups data at three levels of detail in the single dimension of geography.

    Assume the following sales_view table data, where the QUESTION MARK character indicates a null:

     

    This SELECT statement against this table uses ROLLUP in its GROUP BY clause to roll up over state, county, and city: 

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

    The query reports the following data:

     

    This query computes four different levels of grouping, or control breaks:

    1 By state, county, and then city.

    2 By state and then county.

    3 By state.

    4 By all the data in the table.

    Each control break is characterized by a null in the column being rolled up. These nulls represent an empty set, not missing information.

    The following table summarizes this for the current example:

     

    This control break number …

    Breaks on this column set …

    Groups on this column set in the order indicated …

    And has nulls in this column set to represent the rolled up columns…

    1

    City

    1 State

    2 County

    3 City

    None.

    Note that the null in the city column in the second row indicates missing information for that column in that row of the table, not an empty set representing a control break point in the report. See “Example 2: ROLLUP With GROUPING Function” on page 162 for a way to eliminate this ambiguity using CASE expressions.

    2

    City and County

    1 State

    2 County

    City

    3

    City, County, and State

    State

    City and County

    4

    All

    All

    City, County, and State

    Example : ROLLUP With GROUPING Function

    Notice that nulls are used to represent both missing information and the empty set in the report generated in “Example 1: Basic ROLLUP” on page 161. For example, the fifth row of sales_view has a null city in San Diego County, and this is reflected in the report by the second line, where the null maps 1:1 with the null representing the city for the fifth row of the table.

    The nulls for city in lines 5 through 8 of the report indicate an empty set, not missing information. The nulls mean that information is not reported at the level they represent, not that the information is missing from the sales_view base table. This appears to be confounded in row 6 of the report, because there is missing information about one of the reported cities in San Diego county; however, because the information for all cities in San Diego county is summed in this row, there is no problem because it is known that the null city is in that county.

  • The first level of summation, represented symbolically as (state, county), breaks on counties, so in line 5 of the report, all cities are collapsed for Los Angeles County and in line 6 all cities are collapsed for San Diego County.
  • The second level of summation, represented symbolically as (state), breaks on states, so in line 7, all cities and counties are collapsed for the state of California.
  • The final level of summation reports the sum across all states, and line 8 reflects this level of summation. Because data for only one state, California, is recorded in the sales_view table, the totals for lines 7 and 8 are identical. This would not be the case had there been additional state data recorded in the sales_view table.

    • You can use CASE expressions with the GROUPING function (see SQL Functions, Operators, Expressions, and Predicates) to better distinguish between nulls that represent missing information and nulls that represent the empty set, by reporting instances of the empty set using a character string instead of the QUESTION MARK character. In this example, the character string representing the empty set is the phrase (‑all‑): 

         SELECT CASE GROUPING(state)
                 WHEN 1 
                 THEN '(-all-)' 
                 ELSE state 
                END AS state,
                CASE GROUPING(county)
                 WHEN 1 
                 THEN '(-all-)' 
                 ELSE county 
                END AS county,
                CASE GROUPING(city)
                 WHEN 1 
                 THEN '(-all-)' 
                 ELSE city 
                END AS city,
         SUM(margin) 
         FROM sales_view
         GROUP BY ROLLUP (state, county, city);

    This query reports the identical information as the previous query, but the representation is much cleaner: