聚合函数

聚合函数总览

AVG() - 返回集合的平均值。
COUNT() - 返回集合中的项目数。
MAX() - 返回集合中的最大值。
MIN() - 返回集合中的最小值。
SUM() - 返回集合中所有或不同值的总和。
除COUNT()函数外，SQL聚合函数忽略null值。

{
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,-- 返回组中项目的数量，不计算空值NULL
    -- COUNT(*) AS total_records，-- 返回所有项目的数量，不管是不是空值NULL
    COUNT(DISTINCT client_id) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'

---

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

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

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

HAVING

{
SELECT 
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices-- WHERE在分组前进行筛选，可以用任何列
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5-- HAVING在分组后进行筛选，只能用SELECT的列

---

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 
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state , city WITH ROLLUP-- ROLLUP 只能应用于聚合值的列，汇总数据（MYSQL特有的）

---

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-- 这里不能用别名，因为执行到groupby时，还没执行到select中的别名，所以别名还没生效。
}

注

sql语句的执行顺序为：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION -> ORDER BY
首先得知道聚合函数是对结果集运算的，当在where子句使用聚合函数时，此时根据group by 分割结果集的子句还没有执行，此时只有from 后的结果集。所以无法在where子句中使用聚合函数。

HAVING可以用别名是一个特例，mysql对此作了扩展。在mysql 5.7.5之前的版本，ONLY_FULL_GROUP_BY sql mode默认不开启。在5.7.5或之后的版本默认开启。

聚合函数会自己聚合，使用聚合函数需要配合GROUP BY
WHERE不能使用聚合函数，HAVING中可以使用聚合函数
当ORDERBY和GROUPBY同时使用时，ORDERBY里的列必须出现在GROUPBY中