聚类函数,汇总,分组筛选,ROLLUP

max() min() sum() avg() 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 * 1.1) as number_of_invoices,
    count(payment_date) as count_of_payments, -- 空值不会参与计数
    count(*) as total_records
    count(distinct client_id) as number_of_clients
 from invoices
 where invoice_date >= '2019-07-01'

group by 子句

select 
    state,
    city,
    sum(invoice_total) AS total_sales
from invoices 
join clients using (client_id)
where invoice_date >= '2019-07-01'
group by state, city
order by total_sales desc

having 子句

select 
    client_id,
    sum(invoice_total) as total_sales
from invoices
group by client_id 
having total_sales > 500 -- 对于汇总后的字段使用having,使用where会产生找不到total_sales字段的报错
						-- having 子句筛选的字段,必须是select子句中有的字段

ROLLUP 子句

select 
    state,
    city,
    sum(invoice_total) as total_sales
from invoices
join clients using (client_id)
group by state, city with rollup -- ROLLUP操作符会对每个分组进行求和,这里会看到每个分组的求和,以及整个结果集的求和 (mysql特有)
								-- 如果使用了ROLLUP操作符,就不能在group by子句中使用别名