15.00 - Joins and Aggregates on a View - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Joins and Aggregates on a View

Create a report for a set of times and for each destination that includes an average and a maximum value of the count and sums. The purpose of the report is to determine potential loss of revenue by destination.

Create the view loss_summary_view.

     CREATE VIEW loss_summary_view (week, from_code, to_code, count_a, 
                                    sum_x, sum_y, sum_z)
     AS SELECT c.week, h.from_code, h.to_code, COUNT(h.a),SUM(h.x),                SUM(h.y), SUM(h.z)
        FROM history AS h, calendar AS c
        WHERE c.month = 100610
        AND   c.day = h.day
        GROUP BY 1, 2, 3 ;

The SELECT request that uses loss_summary_view to create the report looks like this.

     SELECT lsv.week, ld.to_location, AVG(lsv.count_a),             MAX(lsv.count_a),AVG(lsv.sum_x), MAX(lsv.sum_x),             AVG(lsv.sum_y), MAX(lsv.sum_y), AVG(lsv.sum_z), 
     FROM  loss_summary_view AS lsv, location_description AS ld
     WHERE lsv.to_code = ld.to_code
     GROUP BY 1, 2;

This example joins the cust_file table with the aggregate view cust_prod_sales to determine which companies purchased more than $10,000 worth of item 123:

     CREATE VIEW cust_prod_sales (custno, pcode, sales)
     AS SELECT custno, pcode, SUM(sales)
        FROM sales_hist
        GROUP BY custno, pcode;

The SELECT request that uses cust_prod_sales to create the report looks like this.

     SELECT company_name, sales
     FROM cust_prod_sales AS a, cust_file AS b
     WHERE a.custno = b.custno
     AND   a.pcode = 123
     AND   a.sales > 10000;