聚合函数与分组查询练习

题目

1. 计算每个州(State)的客户数量
2. 查询每个客户的总订单数，显示客户姓名和订单数
3. 算每种产品的销售总量，按销量降序排列
4. 查询总销售额超过 $50 的订单
5. 计算每个运输商处理的订单数量
6. 查询库存量(quantity_in_stock)少于平均库存量的产品

   ---

1.

计算每个州(State)的客户数量

输入

SELECT state, COUNT(*) AS state_count
FROM customers
GROUP BY state;

输出

---

2.

查询每个客户的总订单数，显示客户姓名和订单数

输入 

SELECT orders.customer_id,
	   CONCAT(customers.first_name,' ', customers.last_name) AS cus_name,
	   COUNT(orders.customer_id) AS order_num
FROM orders
INNER JOIN customers ON customers.customer_id=orders.customer_id
GROUP BY orders.customer_id, customers.first_name, customers.last_name 
ORDER BY orders.customer_id;

输出

---

3.

计算每种产品的销售总量，按销量降序排列

输入

SELECT product_id, SUM(quantity) AS prod_count
FROM order_items
GROUP BY product_id
ORDER BY prod_count DESC;

输出

---

4.

查询总销售额超过 $50 的订单

输入

SELECT order_id, SUM(quantity*unit_price) AS price_count
FROM order_items
GROUP BY order_id
HAVING price_count>50
ORDER BY price_count DESC;

输出

---

5.

计算每个运输商处理的订单数量

输入

SELECT orders.shipper_id, 
	   shippers.name,
	   COUNT(*) AS shipper_count
FROM orders
INNER JOIN shippers ON shippers.shipper_id=orders.shipper_id
GROUP BY shipper_id, shippers.name
ORDER BY shipper_id;

输出

 注意

SELECT中所有的非聚集列，必须出现在GROUP BY子句中。

---

6.

查询库存量(quantity_in_stock)少于平均库存量的产品

输入

SELECT product_id, 
	   name, 
       quantity_in_stock,
       (SELECT AVG(quantity_in_stock) FROM products) AS avgstock
FROM products
WHERE quantity_in_stock < (SELECT AVG(quantity_in_stock) FROM products)
ORDER BY quantity_in_stock;

输出