第五章 1-聚合函数aggregate functions--Mosh my SQL

这章我们会学习如何给数据写查询和进行汇总，比如按照客户或者每个地区进行报告销售，本章的内容，执行业务常见，很有用，特别在大型数据机构工作。

SQL自带一些函数，有一些叫聚合函数,如max(),min(),avg,(),sum(),count()

1,单个

select max(invoice_total)
from invoices

select max(invoice_total)
from invoices

2，多个。不管是单个还是多个查询，除了*返回的都是非空值，空值不计入

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(invoice_date) as count_of_payment   #不是所有的都已经支付，只显示已支付的
count(*) as total_records     #显示所有记录数，空值也计入
from invoices

3，筛选2019年下半年的数据，这里我们发现total也随之改变。

select
select
max(invoice_total) as highest,
min(invoice_total) as lowest,
avg(invoice_total) as average,
sum(invoice_total*1.1) as total,
count(*) as total_records
from invoices
where invoice_date>'2019-07-01'

4，distinct 去重invoices里面有多条客户5号的记录，但我们现在只想看这里面有几个客户有记录，最终结果为3,

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
from invoices
where invoice_date>'2019-07-01'

5，练习

对invoices表编写一段查询，得到如下

答案

select
'First half of 2019'as date_range,
sum(invoice_total) as total_sales,
sum(payment_total) as tatal_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 tatal_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 tatal_payments,
sum(invoice_total-payment_total) as what_we_expect
from invoices
where invoice_date between'2019-01-01'and '2019-12-31'

结果如下

为什么total_payments不一样呢？