5.1聚合函数
MAX()
MIN()
AVG()
SUM()
COUNT()
聚合函数后一定要紧跟(，不能有空格否则会报错
聚合函数只运行非空值
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total) AS total,
COUNT(invoice_total) AS number_of_invoices,
FROM invoices
要想用count得到所有记录包括非空值可以用
COUNT (*) AS total_records
在上述代码中加WHERE可以进行筛选计数，但是如果要求没有重复值要用distinct,如下
COUNT (DISTINCT client_id) AS total_records
5.2GROUP BY子句
ORDER BY 升序排列
ORDER BY DESC降序排列
GROUP BY 分组聚合将数据按照一个或多个列进行分组，然后对每个组进行统计计算
SELECT client_id,SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC
SELECT date,payment_method,SUM(payment_total) AS total_payments
FROM payments p
JOIN invoices i
USING (client_id)
JOIN payment_methods pm
ON pm.payment_method_id = p.payment_method
GROUP BY date,payment_method
ORDER BY date
5.3HAVING子句
where子句要在group by前写，但是有时还未分组此时用where子句得不到想要的结果，此时用having子句，可以在分组之后筛选数据（where在分组前筛选数据，having在分组后筛选数据）
SELECT client_id,SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales >500
5.4ROLLUP运算符
with rollup只能应用于聚合值的列
SELECT state,city,SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c
USING (client_id)
GROUP BY state,city WITH ROLLUP
