﻿ SQL - View to keep track the number of customers ordering

# SQL Exercises: View to keep track the number of customers ordering

## SQL VIEW : Exercise-5 with Solution

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

Sample table: orders

Sample table: customer

Sample Solution:

``````-- Creating a VIEW named 'totalforday'
CREATE VIEW totalforday

-- Selecting columns 'ord_date', count of distinct 'customer_id', average 'purch_amt', and total 'purch_amt' from the 'orders' table
-- Grouping the result by the 'ord_date' column
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)
```

Code Explanation:

The SQL statement creates a view called "totalforday" that shows the order date, the total number of distinct customers who made orders on that date, the average purchase amount for orders made on that date, and sums the total purchase amount for orders made on that date.
The results are grouped by order date.

Inventory database model: