SQL的case when语法问题

[jonglin]

在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.

[WWWWA]

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;

[ACMAIN_CHM]

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;

[jonglin]

[Quote=引用 1 楼 的回复:]

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;
[/Quote]

即使换成这样写，也是不行，貌似Access中，when是不可用的；谢谢你

[jonglin]

[Quote=引用 2 楼 的回复:]

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;
[/Quote]

正解，谢谢

ps：Access是不是也不支持CAST; 例如：LTRIM(CAST(SUM(IIF((verdict) = 'failed' , 1 , 0 ))*100.0/COUNT(*)))

[wwwwb]

示例：
(SUM(IIF(verdict= 'failed' , 1 , 0 ))*100.0/COUNT(*)) & '%'

[ACMAIN_CHM]

[Quote]ps：Access是不是也不支持CAST; 例如：LTRIM(CAST(SUM(IIF((verdict) = 'failed' , 1 , 0 ))*100.0/COUNT(*)))[/Quote]是的。ACCESS本身不支持

[jonglin]

[Quote=引用 5 楼 的回复:]
示例：
(SUM(IIF(verdict= 'failed' , 1 , 0 ))*100.0/COUNT(*)) & '%'
[/Quote]

转换成百分比的形式，此方法正确；已验证