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

Last update on December 20 2024 12:05:18 (UTC/GMT +8 hours)

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:

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..