MYSQL-聚合函数

聚合函数

汇总数据

*常见的聚合函数
MAX(),IMN(),AVG(),SUM(),COUNT()

SELECT MAX ( C) AS hightes,
MIN(C * 1.5) AS lowest,
AVG ( C) AS average,
COUNT(DISTINCT C1) AS n1
– COUNT(*) （计算全部行数）
FROM T （只运行非空值，跳过空值）
WHERE C2 > ‘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) - SUM(payment_total) as what_we_expect
FROM invoices
where invoice_date BETWEEN ‘2019-07-01’ AND ‘2019-12-31’
Union
SELECT ‘TOTAL’,
SUM(invoice_total),
SUM(payment_total),
SUM(invoice_total)-SUM(payment_total)
FROM invoices

分组

*分组顺序（GROUP BY）
SELECT
SUM(C1) AS A，
COUNT(C2) AS B
FROM T1
JOIN T2 USING (C2)
WHERE C3>‘2019-07-01’
GROUP BY C2,C4
– GROUP BY C2,C4 WITH ROLLUP（用于聚合列的汇总，如果GROUP BY 有1列，就是汇总1列所有的，得到1行汇总数；如果有2列，就按C1,C2汇总，最后再总汇总，得到N+1行的汇总数，与此类推…）
ORDER BY C2 DESC
– HAVING A>500 AND B>5（对分组之后的表进行再筛选）