SQL-Aggregate Functions

最新推荐文章于 2023-10-18 21:53:19 发布

原创

最新推荐文章于 2023-10-18 21:53:19 发布 · 690 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

这篇博客通过一系列SQL语句实例，展示了如何使用聚合函数找到所有订单的总购买金额、平均购买金额、销售员数量、客户数量、特定条件的客户数等。还探讨了`COUNT`函数中`ALL`关键字的作用，以及`HAVING`和`WHERE`子句在不同情况下的使用区别。同时，提供了计算最高购买金额、按客户ID和日期排列的最高购买金额等复杂查询的例子。

                    
                    Write a SQL statement to find the total purchase amount of all orders.SELECT SUM(purch_amt)
FROM orders;
 
Write a SQL statement to find the average purchase amount of all orders.SELECT AVG(purch_amt)
FROM orders;
 
Write a SQL statement to find the number of salesmen currently listing for all of their customers.SELECT COUNT (DISTINCT salesman_id) 
FROM orders;
 
Write a SQL statement know how many customer have listed their names.SELECT COUNT (DISTINCT cust_name) 
FROM consumer;
 
Write a SQL statement find the number of customers who gets at least a gradation for his/her performance.
 
标准答案在count里使用了all，没发现它的作用是什么SELECT COUNT (grade) 
FROM customer; 
------ 标准答案
SELECT COUNT (ALL grade) 
FROM customer;
 
Write a SQL statement to get the maximum purchase amount of all the orders.SELECT MAX(purch_amt)
FROM customer;
 
Write a SQL statement to get the minimum purchase amount of all the orders.SELECT MIN(purch_amt)
FROM customer;
 
Write a SQL statement which selects the highest grade for each of the cities of the customers