SQL Exercises: View to show average and total orders for each salesman
11. From the following table, create a view to compute the average purchase amount and total purchase amount for each salesperson. Return name, average purchase and total purchase amount. (Assume all names are unique.).
Sample table: Salesman
salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution:
-- Creating a VIEW named 'norders'
CREATE VIEW norders
-- Selecting 'name', average of 'purch_amt', and sum of 'purch_amt' for each salesman
-- Joining the 'salesman' and 'orders' tables on 'salesman_id'
-- Grouping the results by 'name'
AS SELECT name, AVG(purch_amt), SUM(purch_amt)
FROM salesman, orders
WHERE salesman.salesman_id = orders.salesman_id
GROUP BY name;
output:
sqlpractice=# SELECT *
sqlpractice-# FROM norders;
name | avg | sum
--------------+-----------------------+----------
Mc Lyon | 1152.7150000000000000 | 2305.43
James Hoog | 2817.8650000000000000 | 11271.46
Pit Alex | 270.6500000000000000 | 270.65
Lauson Hense | 1295.4500000000000000 | 2590.90
Paul Adam | 87.6450000000000000 | 175.29
Nail Knite | 466.3166666666666667 | 1398.95
(6 rows)
Code Explanation:
The above statement in SQL creates a view called norders that calculates the average and total purchase amounts for each salesman and returns the results in the columns are name, AVG(purch_amt), and SUM(purch_amt).
The JOIN clause joins the salesman and orders tables on the salesman_id column. The results are grouped by the name column.
Go to:
PREV : View that shows the number of salesman in each city.
NEXT : View to shows each salesman has more than one customer..
Inventory database model:
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.