GROUPING SETS Option Example | SQL SELECT Statements | Teradata Vantage - 17.05 - GROUPING SETS Option Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.