w3resource

SQL Exercises: View to show average and total orders for each salesman

SQL VIEW : Exercise-11 with Solution

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


Sample table: orders


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.

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous SQL Exercise: View that shows the number of salesman in each city.
Next SQL Exercise: View to shows each salesman has more than one customer..

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.