CUBE Option Example | SQL SELECT Statements | Teradata Vantage - CUBE Option Example - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
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.