聚合函数、汇总、分组筛选、ROLLUP

Aggregate Functions（聚合函数）

-- 比如，我们需要统计报告客户或者地域的销售数据，这样我们就能看到在不同州销售的情况
SELECT 
	-- 取值
	MAX(payment_date) AS highest_date,
	MAX(invoice_total) AS highest,
	MIN(invoice_total) AS lowest,
	AVG(invoice_total) AS average,
	-- 计算
	SUM(invoice_total) AS total,
	SUM(invoice_total * 1.1) AS total2, 	-- 可以在里面计算
	COUNT(invoice_total) AS number_of_invoices,
	COUNT(payment_total) AS count_of_payments,
	COUNT(*) AS toal_records,	-- 计算全部
	-- 去重
	COUNT(DISTINCT client_id) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'

-- 有四列，分别是日期范围，总销售额，总收款额和总应收额也就是这两个的差值
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-30'
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
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-30'

GROUP BY（分组筛选）

-- 日期，支付方式和支付金额
-- 以date和信用卡名字分组、以日期排序
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 pm.`name`,date		-- 此时返回的就是date和payment对于的total_payments:多条对应的数据
ORDER BY date

Having（分组后筛选数据 且 必须是选中的数据）

-- Get the customers
-- 	  located in Virginia
-- 	  who have spent more than $100

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 USING (customer_id)		-- 订单
JOIN order_items oi USING (order_id)		-- 订单详情
WHERE state = 'VA'
GROUP BY
	c.customer_id,
	c.first_name,
	c.last_name
HAVING total_sales > 100

ROLLUP操作符（统计操作符：只作用于聚合函数）

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