select
--Sdate '时间/十分钟',
case grouping(Sdate)
when 0 then Sdate
when 1 then '总计'
end as '时间/十分钟',
count (distinct orderDeputyID) as '下单数',
sum (ticketCount) as '票张数',
count ( distinct payorderDeputyID ) '支付订单数',
sum (payticketCount) '支付票单数',
count (distinct orderDeputyID) -(count ( distinct payorderDeputyID )) '未支付订单数',
sum (ticketCount) - sum (payticketCount) '未支付票单数'
from (
select substring(CONVERT(varchar(100), dateTime, 120),1,15) as Sdate
,orderDeputyID
,ticketCount
,case when orderPayment=1 then orderDeputyID
else null
end as payorderDeputyID,
case when orderPayment=1 then ticketCount
else 0
end as payticketCount
from [order] where sessionId =XXXX and orderType=9
)T group by Sdate with Rollup