【MySQL】汇总数据

聚合函数

MAX()
MIN()
AVG()
SUM()
COUNT()
注意：

• 括号内可以为列名，也可以为计算表达式
• 字母和括号间没有空格
• 括号内选中的列只有非空参与运算，若使用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,
    COUNT(payment_date) AS count_of_payments,
    COUNT(*) AS total_records
FROM invoices

SELECT 
	MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total * 1.1) AS total,
    COUNT(DISTINCT client_id) AS total_records
-- 计算不重复项要添加distinct
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

分类汇总

单列汇总

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

 

 没用group by 是对invoice_total单纯求和，用了group by后对invoice_total根据client_id分类求和

多列汇总

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

 

 HAVING

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices < 6

 

注意：

• where可以用于分组前筛选数据，而having可以在分组后筛选数据
• having后面的列必须是select中选中的，而where无此限制

WITH ROLLUP

会得到额外一行用于汇合是聚合值的列
注意：并不是所有SQL语言都可以用，只有MySQL可以！

SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city  WITH ROLLUP