我有以下疑问:
SELECT ACCT_OT,
COUNT(CASE WHEN BR_CD like '%0%' THEN 1 ELSE NULL END) AS new,
COUNT(CASE WHEN BR_CD like '%1%' THEN 1 ELSE NULL END) AS old,
FROM MSTR_TBL where ACCT_OT between '2017-10-23' and '2017-10-25'
GROUP BY ACCT_OT;
我希望能够在同一查询中添加另一列,其中显示旧/新的百分比。例如在10/23上,new是10,old是1,那么第三列将是10%。希望你们能帮忙。
4条答案
按热度按时间n53p2ov01#
可能是使用常用的表表达式。像这样:
lmvvr0a82#
你能做到的
gwbalxhn3#
我会用
sum()
而不是count()
:如果你想要百分之旧,我会:
但是,比率需要重复以下表达式:
我移除了
else 0
避免被零除。t3irkdon4#
我不确定这是否有效,因为我不能测试它,但你可以试试。
SELECT ACCT_OT, COUNT(CASE WHEN BR_CD like '%0%' THEN 1 ELSE NULL END) AS new, COUNT(CASE WHEN BR_CD like '%1%' THEN 1 ELSE NULL END) AS old, CONVERT(VARCHSR(50), COUNT(CASE WHEN BR_CD like '%1%' THEN 1 ELSE NULL END) * 100 / COUNT(CASE WHEN BR_CD like '%0%' THEN 1 ELSE NULL END))+'% ' as percentage FROM MSTR_TBL where ACCT_OT between '2017-10-23' and '2017-10-25' GROUP BY ACCT_OT;