ROLLUP Option Examples | SQL SELECT Statements | VantageCloud Lake - ROLLUP Option Examples - Teradata Vantage

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 ales_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:

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:

Example: ROLLUP with GROUPING Function

Nulls are used to represent both missing information and the empty set in the report generated in Example: Basic ROLLUP. 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 represented level, not that information is missing from the sales_view base table. This appears confounded in row 6, because information is missing about one reported city in San Diego county. However, because the information for all cities in San Diego county is summed in this row, you know 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

You can use CASE expressions with the GROUPING function 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: