16.20 - Alternative: Using Derived Tables - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

prodname
Advanced SQL Engine
Teradata Database
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1145-162K

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
... ... ... ...