5. 聚合函数

聚合函数

use sql_invoicing;

select 
	max(invoice_total) as highest,
    min(invoice_total) as lowest,
    avg(invoice_total) as average,
    sum(invoice_total) as total,
    sum(invoice_total * 10) as total_10,		-- 可以使用表达式
    count(invoice_total) as number_of_invoices,
    count(payment_date) as count_of_payments,	-- 返回的是非空的记录数量
    count(*) as total_records,
    count(distinct client_id) as total_records	-- 得到唯一值
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) - sum(payment_total) as what_we_expect
from invoices
where invoice_date < '2019-07-01'
union
select
	'Seoncd 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 > '2019-07-01'
union
select
	'Total' 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
    

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

**注意：必须按照from where group by order by的顺序写语句，否则会报错

select
	state,
    city,
	sum(invoice_total) as total_sales
from invoices i
join clients using (client_id)
group by state,city

select
	date,
    pm.name as payment_methon,
    sum(amount) as total_payments
from payments p
join payment_methods pm
	on p.payment_method = pm.payment_method_id
group by date
order by date

having

不能使用where，因为total_sales是在分组之后才有的。 having专属在group之后，可以实现这个需求

select 
	client_id,
    sum(invoice_total) as total_sales
from invoices
-- where total_sales > 500		-- 不能使用where，因为total_sales是在分组之后才有的
group by client_id
having total_sales > 500

having 中还可以使用复合判断语句。

select 
	client_id,
    sum(invoice_total) as total_sales,
    count(*) as number_of_invoices
from invoices
-- where total_sales > 500		-- 不能使用where，因为total_sales是在分组之后才有的
group by client_id
having total_sales > 500 and number_of_invoices > 5

-- Get the customers
-- located in Virginia
-- who have spent more than $100

use sql_store;

select 
	first_name,
    sum(quantity*unit_price) as total_fee
from customers
join orders o using(customer_id)
join order_items oi using(order_id)
where state = 'VA'
group by customer_id
having total_fee > 100

with rollup

GROUP BY …… WITH ROLL UP 自动汇总型分组，若是多字段分组的话汇总也会是多层次的，注意这是MySQL扩展语法，不是SQL标准语法

use sql_invoicing;

select
	client_id,
    sum(invoice_total) as total_sales
from invoices 
group by client_id with rollup;

select
	name as payment_method,
    sum(p.amount) as total
from payments p
join payment_methods pm 
	on pm.payment_method_id = p.payment_method
where payment_method in (1,2)
group by pm.name with rollup

Cash	10.00
Credit Card	351.38
	361.38