Examples - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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:

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
1    30000    33000      3000 CA San Diego ?

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:

state
county
city
SUM(margin)
-----
------
----
-----------
CA
San Diego
San Diego
19500
CA
San Diego
?
3000
CA
Los Angeles
Avalon
14400
CA
Los Angeles
Long Beach
24300
CA
Los Angeles
?
38700
CA
San Diego
?
22500
CA
?
?
61200
?
?
?
61200
This query computes four different levels of grouping, or control breaks by:
  1. state, county, and then city.
  2. state and then county.
  3. state.
  4. 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 State

County

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: ROLLUP With GROUPING Function for a way to eliminate this ambiguity using CASE expressions.

2 City and County State

County

City
3 City, County, and State State City and County
4 All All City, County, and State

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

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