mysql day4

– 聚合函数,只运行非空树枝
– 汇总数据
– MAX()
– MIN()
– AVG()
– SUM()
– COUNT()
– count(invoice_total) as number_of_invoices,-- 返回非空
– count(payment_date) as count_of_payments,
– select
– max(invoice_total) as highest,
– min(invoice_total) as lowest,
– avg(invoice_total) as average,
– sum(invoice_total * 1.1) as total, – 先得到列值，再乘1.1

– – count(client_id) 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-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-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
– where invoice_date
– between ‘2019-01-01’ and ‘2019-12-31’

– 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 – 按照销售总额降序排列

– 多列分组排序
– 得到每个州和城市的销售总额
– 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_method,
– sum(amount) as total_payments
– from payment_methods pm
– join payments p
– on pm.payment_method_id = p.payment_method
– group by date,payment_method – 进行分组
– order by date
– select
– client_id,
– sum(invoice_total) as total_sales,
– count(*) as number_of_invoices – 筛选出发票的数量
– from invoices
– -- where total_sales > 500 – 不能用where，因为此时还没有分组，用having子句
– group by client_id
– having total_sales > 500 and number_of_invoices > 5 – 分组后筛选,子列只能选select中存在的

– 练习
– use sql_store;
– select
– c.customer_id,
– c.first_name,
– c.last_name,
– sum(oi.quantity * oi.unit_price) as total_sales
– from customers c
– join orders o using(customer_id)
– join order_items oi using(order_id) – 必须先与orders连接再和orderitems连接，因为customers中没有orderid
– where state = ‘VA’
– group by
– c.customer_id,
– c.first_name,
– c.last_name
– having total_sales > 100

– with rollup运算符
– 只能汇总用于聚合值的列
– use sql_invoicing;
– select
– state,
– city,
– sum(invoice_total) as total_sales
– from invoices i
– join clients c using (client_id)
– group by state,city with rollup – 看到每一组和整个结果集的汇总值

– 习题
– 每种支付方式的总额
– use sql_invoicing;
– select
– pm.name as payment_method,
– sum(p.amount) as total
– from payment_methods pm
– join payments p on pm.payment_method_id = p.payment_method
– group by pm.name with rollup – 不能在rollup里使用别名