【Stef-SQL查询】查询求百分比,求大侠们相助

【Stef-SQL查询】查询求百分比,求大侠们帮助
表A

 name status

 A1 Pass
 A2 Fail
 A3 Pass
 A2 Pass
 A2 Pass
 A4 Pass
 A5 Pass
 A2 Fail
 A5 Pass
 A6 Pass
 A2 Fail

 如何写sql语句，得出以下结果：
name StatusTime Pass Pass-Ratio  

A2 5 2 40%

[解决办法]

SQL code

--> --> (Roy)生成測試數據 if not object_id('Tempdb..#') is null    drop table #GoCreate table #([name] nvarchar(2),[status] nvarchar(4))Insert #select N'A1',N'Pass' union allselect N'A2',N'Fail' union allselect N'A3',N'Pass' union allselect N'A2',N'Pass' union allselect N'A2',N'Pass' union allselect N'A4',N'Pass' union allselect N'A5',N'Pass' union allselect N'A2',N'Fail' union allselect N'A5',N'Pass' union allselect N'A6',N'Pass' union allselect N'A2',N'Fail'GoSelect [name],StatusTime=COUNT(1),Pass=sum(case when [status]=N'Pass' then 1 else 0 end),[Pass-Ratio]=str(sum(case when [status]=N'Pass' then 1 else 0 end)*100.0/COUNT(1),6,2)+'%'from # group by [name]/*name    StatusTime    Pass    Pass-RatioA1    1    1    100.00%A2    5    2     40.00%A3    1    1    100.00%A4    1    1    100.00%A5    2    2    100.00%A6    1    1    100.00%*/
[解决办法]
SQL code
SELECT name,COUNT(*) as statusTime,SUM(case when status='pass' then 1 else 0 end) as pass,CONVERT(varchar, CONVERT(float,SUM(case when status='pass' then 1 else 0 end))/COUNT(*)*100)+'%' as PassRatio  FROM A  GROUP BY name