如何做下面这个查询:
表 id shop(商户),创建人(user),type(类型), amount(金额) status (状态) ,status可以是0,1,2,3,4 , 4表示成功,
我如何统计
商户,创建人,类型, 交易总笔数,交易总金额,成功总笔数,成功总金额
(每一条记录算一笔交易)
表 id shop(商户),创建人(user),type(类型), amount(金额) status (状态) ,status可以是0,1,2,3,4 , 4表示成功,
我如何统计
商户,创建人,类型, 交易总笔数,交易总金额,成功总笔数,成功总金额
(每一条记录算一笔交易)
SELECT shop,USER,TYPE,
Count(1) 交易总笔数,
Sum(amount) 交易总金额,
Sum(Decode(status,4,1,0))成功总笔数 ,
Sum(Decode(status,4,amount,0)) 成功总金额
FROM tab
GROUP BY shop,USER,TYPE
sum(decode(status,4,1,0)) 成功总笔数,
sum(decode(status,4,amount,0)) 成功总金额
from tb
group by shop,user,type
shop,
user,
type,
count(*) 交易总笔数,
sum(amount) 交易总金额,
count(case when status='4' then 1 else 0 end) 成功总笔数,
sum(case when status='4' then amount else 0 end) 成功总金额
from tablea group by shop,user,type
select shop 商户,user 创建人,type 类型,count(*) 交易总笔数,sum(amount) 交易总金额,
sum(decode(status,4,1,0)) 成功总笔数,
sum(decode(status,4,amount,0)) 成功总金额
from tb
group by shop,user,type