with rollup :汇总数据的强大的运算符,只有在mySQL有,SQL server或者oracle没有。但是应该有类似的,可能名字不同。
1,比如汇总【所有客户】的总销售额,
select
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id with rollup
2,
select
state,
city,
sum(invoice_total) as total_sales
from invoices i
join clients c using(client_id)
group by state,city with rollup
3,练习
根据如下结果,用payments写查询
答案:
select
pm.name as payment_method,
sum(amount)as total
from payments p
join payment_methods pm on p.payment_method=pm.payment_method_id
group by pm.name with rollup
注意最后加粗红色字体不能写成payment_method,需要输入实际的列的名称,不能使用别名alias