﻿ SQL - View to show average and total orders for each salesman

# 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: