Alternative: Using Derived Tables

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

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
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
... ... ... ...