SQL的case when语法问题
在Access数据库中的一张表，有两个字段：CaseName 和result
result的值分为pass和failed， 现在要统计一下，某个casename一共出现了多少次，其中pass了多少，failed多少
我写的sql如下：
SELECT CaseName AS 用例名, COUNT(*) AS 总次数, SUM(CASE (verdict) when 'pass' THEN 1 ELSE 0 END) AS 通过, SUM(CASE(verdict) WHEN 'failed' THEN 1 ELSE 0 END) AS 未通过 from tb_case group by CaseName;
但是每次运行的时候，都会提示, "when"处有语法错误miss operator.
[解决办法]
SELECT CaseName AS 用例名, COUNT(*) AS 总次数, SUM(CASE when verdict='pass' THEN 1 ELSE 0 END) AS 通过, SUM(CASE when verdict='failed' THEN 1 ELSE 0 END) AS 未通过 from tb_case group by CaseName;
[解决办法]
ACCESS中改成 IIF
SELECT CaseName AS 用例名, COUNT(*) AS 总次数, SUM(IIF (verdict) = 'pass' , 1 , 0 ) AS 通过, SUM(IIF(verdict) = 'failed' , 1 , 0 ) AS 未通过 from tb_case group by CaseName;
[解决办法]
示例：
(SUM(IIF(verdict= 'failed' , 1 , 0 ))*100.0/COUNT(*)) & '%'
[解决办法]
