第五章 2-Group by 子句the group by clause--Mosh my SQL

1,上一节我们学习了如何汇总数据，这一节我们学习分类汇总

以下是在invoices中按照客户ID分别计算总销售额，并且按照销售额降序排列

select
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id
order by total_sales desc

2，如果要选择下半年的销售额，其他不变

则加上

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

请注意上述的顺序，group by 在where和order by之间

3，如何多列分组，clients 和invoices表join筛选出各州的各个城市销售额

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

4，练习,写出如下表的查询

答案：

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