MYSQL学习笔记-聚合函数（汇总数据）

一、聚合函数

Aggregate function只计算非空值

SELECT 
	MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
-- DISTINCT 关键字可以去除重复
-- COUNT(DISTINCT client_id) AS total_records
    COUNT(client_id) AS total_records
-- COUNT(*) AS total_records
-- *找出所有记录，不论是否是空值 
FROM invoices
WHERE invoice_date > '2019-07-01'

运行结果如下：

练习

表格invoices如下：

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
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'

运行结果如下：

二、GROUP BY子句

用单个列分组

• GROUP BY语句在WHERE之后，ORDER BY之前

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

用多个列分组

payments表格如下：

payment_methods表格如下：

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

运行结果如下：

三、HAVING子句

不能用WHERE语句解决的原因是：如果用了WHERE，这个时候的表格还没有进行分组，GROUP BY 要写在WHERE之后。

WHERE和HAVING的区别：

1. WHERE子句是在分组行之前筛选数据，HAVING子句是在分组行之后筛选数据
2. HAVING子句后的条件必须是SELECT中选中的列，WHERE后的条件可以是任意列

练习

需要得到以下信息：

分析：

• 首先要得到located in VA的顾客
• 接着需要将customers和orders表格连接起来
• 再将orders和order_items表格连接起来
• 计算总花费
• 用id name分组
• 筛选出spent more than 100 的记录

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

运行结果如下：

四、WITH ROLLUP

• 用于聚合值的列，求和
• 如果是多个分组条件，用ROLLUP则会得到每个组及整个结果集的汇总值

练习

求出以下列表

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 name WITH ROLLUP

注意：

• GROUP BY 后的列名称必须是SELECT过的实际列名称，否则会出现
  “Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sql_invoicing.pm.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ”的错误