select 'all',sum(订购数量),
sum(总金额),
sum(case when 订购状态 ='取消' then 订购数量 else 0 end),
sum(case when 订购状态 ='取消' then 总金额 else 0 end) ,
.......
from 订单表
union
select 订单类型,sum(订购数量),
sum(总金额),
sum(case when 订购状态 ='取消' then 订购数量 else 0 end),
sum(case when 订购状态 ='取消' then 总金额 else 0 end) ,
.......
from 订单表
group by 订单类型
select 订单类型=case when grouping(订单类型)=1 then '合计' else 订单类型 end,总数=sum(订购数量),sum(总金额),
sum(case when 订单状态='取消' then 订购数量 end),
sum(case when 订单状态='取消' then 总金额 end),
.........
from tb group by 订单类型 with rollup
select 'all',sum(订购数量),
sum(总金额),
sum(case when 订购状态 ='取消' then 订购数量 else 0 end),
sum(case when 订购状态 ='取消' then 总金额 else 0 end) ,
.......
from 订单表
union
select 'all',sum(订购数量),
sum(总金额),
sum(case when 订购状态 ='取消' then 订购数量 else 0 end),
sum(case when 订购状态 ='取消' then 总金额 else 0 end) ,
.......
from 订单表
where 订单类型= 1
union
select 'all',sum(订购数量),
sum(总金额),
sum(case when 订购状态 ='取消' then 订购数量 else 0 end),
sum(case when 订购状态 ='取消' then 总金额 else 0 end) ,
.......
from 订单表
where 订单类型= 2