Alternative: Using Derived Tables - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

Although only window functions allow aggregates specified together in the same SELECT list, window functions and Teradata-specific functions can be combined with aggregates using derived tables or views. Using derived tables or views also clarifies the semantics of the computation.

Example

The following example shows the sales rank of a particular product in a store and its percent contribution to the store sales for the top three products in each store.

   SELECT RT.storeid, RT.prodid, RT.sales,
   RT.rank_sales, RT.sales * 100.0/ST.sum_store_sales
   FROM (SELECT storeid, prodid, sales, RANK(sales) AS rank_sales
   FROM sales_tbl
   GROUP BY storeID
   QUALIFY RANK(sales) <=3) AS RT,
   (SELECT storeID, SUM(sales) AS sum_store_sales
   FROM sales_tbl
   GROUP BY storeID) AS ST
   WHERE RT.storeID = ST.storeID
   ORDER BY RT.storeID, RT.sales;

The results table might look something like the following.

storeID prodID sales rank_sales sales*100.0/sum_store_sales
1001 D 35000.00 3 17.949
1001 C 60000.00 2 30.769
1001 A 100000.00 1 51.282
1002 D 25000.00 3 25.000
1002 C 35000.00 2 35.000
1002 A 40000.00 1 40.000
1003 C 20000.00 3 20.000
1003 A 30000.00 2 30.000
1003 D 50000.00 1 50.000
... ... ... ...