SQL VIEW Exercises: Create a view to keeping track the number of customers ordering number of salesmen attached, average amount of orders and the total amount of orders in a day

SQL VIEW : Exercise-5 with Solution

5. From the following table, create a view to count the number of unique customer, compute average and total purchase amount of customer orders by each date.

Sample table: orders

Sample table: customer

Sample Solution:

CREATE VIEW totalforday
 AS SELECT ord_date, COUNT(DISTINCT customer_id),
 AVG(purch_amt), SUM(purch_amt)
 FROM orders
 GROUP BY ord_date;

output:

sqlpractice=# SELECT *
sqlpractice-# FROM totalforday;
  ord_date  | count |          avg          |   sum
------------+-------+-----------------------+---------
 2012-04-25 |     1 | 3045.6000000000000000 | 3045.60
 2012-06-27 |     1 |  250.4500000000000000 |  250.45
 2012-07-27 |     1 | 2400.6000000000000000 | 2400.60
 2012-08-17 |     3 |   95.2633333333333333 |  285.79
 2012-09-10 |     3 | 2326.3833333333333333 | 6979.15
 2012-09-22 |     1 |  322.0000000000000000 |  322.00
 2012-10-05 |     2 |  132.6300000000000000 |  265.26
 2012-10-10 |     2 | 2231.9150000000000000 | 4463.83
(8 rows)

Inventory database model:

Contribute your code and comments through Disqus.

Previous: From the following table, create a view to count the number of customers in each grade.
Next: From the following tables, create a view to get the salesperson and customer by name. Return order name, purchase amount, salesperson ID, name, customer name.