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