MySQL入门 | day04 | 聚合函数

聚合函数

聚合函数的特点是只运行非空值

MAX()     MIN()     AVG()     SUM()    COUNT()

SELECT
	MAX(invoice_total) AS higest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    COUNT(invoice_total) AS number_of_invoices
FROM  invoices

去除重复值—DISTINCT

SELECT 
	COUNT(DISTINCT client_id) AS total_records
FROM  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 <= '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

GROUP BY

SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM  invoices
GROUP BY client_id
ORDER BY total_sales DESC

 先分组后排序 

SELECT 
    p.date,
	pm.name AS payment_method,
	SUM(amount) AS toal_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
JOIN invoices
	USING(invoice_id)
GROUP BY p.date, payment_method
ORDER BY p.date

 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 >5

HAVINNG子句要用在GROUP BY语句的后面 分组后在进行筛选

HAVING子句中用到的列要在SELECT子句中存在的，WHERE则不需要

练习：编写一个查询，得到坐标Virginia的顾客，并且消费超过一百

USE sql_store;

SELECT 
	c.customer_id, 
    c.first_name,
    c.last_name,
    SUM(oi.quantity * oi.unit_price) AS total_sale
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_sale>100

ROLLUP运算符 

ROLLUP运算符只能用于聚合函数

用法是在GROUP BY 字段之后 ，可以在分组字段的基础上做一个汇总统计

SELECT 
	state, 
    city, 
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c
	USING(client_id)
GROUP BY state, city WITH 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 pm.name WITH ROLLUP