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