GROUPING SETS Option Example | SQL SELECT Statements | Teradata Vantage - GROUPING SETS Option Example - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.