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

Last update on December 20 2024 11:46:19 (UTC/GMT +8 hours)

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:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to count many customers at each lavel of grade.
Next SQL Exercise: View to show for each order the salesman and customer.