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), MAX(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;