2.1 汇总数据（形成报表）

目录

1. Aggregate Functions聚合函数

【练习】对invoices表编写一个查询，要求查询出以下结果。

2. The GROUP BY Clause GROUP BY字句 

【练习】写一段SQL，要求显示以下结果。

 3. The HAVING Clause HAVING子句

【练习】，写一段SQL，在sql_store库，customers表中查询位于Virginia，且花费超过$100的顾客。表customers如下所示：

4. The ROLLUP Operator ROLLUP运算符

【练习】写一段SQL，能显示出以下结果。

---

1. Aggregate Functions聚合函数

该部分要学会如何为数据汇总写查询，比如按照顾客的要求查询来写报告。

MySQL中有很多内置函数，其中有一部分叫做聚合函数。

例如：

USE sql_invoicing;

SELECT MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,      -- 取最小值
AVG(invoice_total) AS average,     -- 取均值 
SUM(invoice_total) AS summary,     -- 求和
COUNT(invoice_total) AS total,    -- 计数
COUNT(payment_date) AS count_of_payments, 
COUNT(*) AS count_of_records -- 不论是否是空值都计数
FROM invoices;
    

注；

1. 聚合函数只运行非空值，所以如果列中有空值，它不会被算在函数里。

2. 可以对表达式里的内容进行计算，比如invoice_total * 1.1

3. 可以使用筛选器

USE sql_invoicing;

SELECT MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,      -- 取最小值
AVG(invoice_total) AS average,     -- 取均值 
SUM(invoice_total) AS summary,     -- 求和
COUNT(invoice_total) AS total,    -- 计数
COUNT(payment_date) AS count_of_payments,
COUNT(*) AS count_of_records
FROM invoices
	WHERE invoice_date > '2019-07-01'; 

注：默认状态下，会取重复值，此时需要在函数中使用distinct关键词

COUNT(DISTINCT clinet_id) AS total_records

【练习】对invoices表编写一个查询，要求查询出以下结果。

USE sql_invoicing;

SELECT
'First half of 2019' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
	WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
    
UNION
    
SELECT
'Second half of 2019' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
	WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'

UNION
    
SELECT
'Total' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices;

2. The GROUP BY Clause GROUP BY字句 

SELECT 
	SUM(invoice_total) AS total_sales
    FROM invoices;

通过该语句，可以查询到所有invoice_total的求和，但是并不知道每一个客户总销售，因此可以使用GROUP BY语句。

SELECT 
    client_id,
	SUM(invoice_total) AS total_sales
    FROM invoices
			WHERE invoice_date > '2019-07-01'
	GROUP BY client_id
	ORDER BY total_sales DESC

可以看到，通过GROUP BY将总销售额按客户进行求和，并且按照total_sales的降序排序。

顺序：

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. ORDER BY

SELECT 
	state,
    city,
	SUM(invoice_total) AS total_sales
    FROM invoices
    JOIN clients USING (client_id)
	GROUP BY state, city

【练习】写一段SQL，要求显示以下结果。

SELECT 
	date,
    pm.name AS payment_method,
    SUM(amount) AS total_payments
    
    FROM payments p
        JOIN payment_methods pm
        ON p.payment_method = pm.payment_method_id
	GROUP BY date, payment_method
	ORDER BY date

 3. The HAVING Clause HAVING子句

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id

该段代码将每个客户的销售额进行了加总，现在要求（只想包含>500美金的客户），应该如何去做。

即，不想要客户2的销售额，因为其低于500美金。

注；此时无法使用where total_sales > 500进行，因为它在GROUP BY之前。

此时可使用HAVING子句，它可在分组后进行筛选。

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500

因此，在GROUP BY前使用WHRER，在之后使用HAVING

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices  
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5

【练习】，写一段SQL，在sql_store库，customers表中查询位于Virginia，且花费超过$100的顾客。表customers如下所示：

USE sql_store;

SELECT 
	c.customer_id,
    c.first_name,
    c.last_name,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE state = 'VA'
GROUP BY 
	c.customer_id,
    c.first_name,
    c.last_name
HAVING total_sales > 100

4. The ROLLUP Operator ROLLUP运算符

USE sql_invoicing;
SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP -- 可以获得汇总信息

其中新增的一行client_id，是total_sales的求和。

【练习】写一段SQL，能显示出以下结果。

USE sql_invoicing;

SELECT 
	pm.name AS payment_method,
    SUM(amount) AS total
    FROM payments p 
    JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
    GROUP BY pm.name WITH ROLLUP