CUBE Option Example | SQL SELECT Statements | Teradata Vantage - CUBE 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™

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
2    20000    24750       4750 CA Los Angeles Long Beach
2      4800      5940       1140 CA San Diego Santee
1    57600    71280     13680 CA San Diego San Diego

You are interested in determining the effect of county and PID on sale price.

The following SELECT statement analyzes the effect of county and PID on sale price using CUBE in its GROUP BY clause:

     SELECT pid, county, SUM(sale)
     FROM sales_view
     GROUP BY CUBE (pid,county);

The query reports the following data, where the QUESTION MARK character indicates a null:

PID County Sum(sale)
--- ------ ---------
2 Los Angeles 24750
1 Los Angeles 164475
2 San Diego 5940
1 San Diego 154155
2 ? 30690
1 ? 381630
? Los Angeles 189225
? San Diego 160095
? ? 349320
This query computes four different levels of grouping over the dimensions of county and PID by:
  • county and PID (groups 1 through 4).
  • PID only (groups 5 and 6).
  • county only (groups 7 and 8).
  • Nothing (group 9), which aggregates sales over all counties and PIDs.

Notice that nulls are used to represent the empty 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.