有关统计问题。。。
碰到一个问题，请教高手们。。
有一张表如下数据
现在想做一个统计。。
得到如下数据
TaskID IsCall IsMsg IsFax IsEmail total
12 1/3 0/3 0/3 0/3 1
23 1/3 0/3 0/3 0/3 1
13 1/2 1/2 0/2 0/2 1
这里 isCall 1/3 是根据taskID分组而来的，，这个任务有三条客户
IsCall 有一条 True 两条False 所以得出 1/3
请教 这种统计有没有好的效率高点的查询，，难道真的只有动态SQl可以实现吗。
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-26 13:16:13
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([taskid] int,[iscall] varchar(5),[ismsg] varchar(5),[isfax] varchar(5),[isemail] varchar(5))
insert [huang]
select 12,'false','false','false','false' union all
select 12,'false','false','false','false' union all
select 12,'true','false','false','false' union all
select 23,'false','false','false','false' union all
select 23,'false','false','false','false' union all
select 23,'true','false','false','false'
--------------开始查询--------------------------
select [taskid] ,CONVERT(VARCHAR(2),sum(CASE WHEN [iscall]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT(iscall))[iscall],
CONVERT(VARCHAR(2),sum(CASE WHEN [ismsg]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT([ismsg]))[ismsg],
CONVERT(VARCHAR(2),sum(CASE WHEN [isfax]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT([isfax]))[isfax],
CONVERT(VARCHAR(2),sum(CASE WHEN [isemail]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT([isemail]))[isemail]
from [huang]
GROUP BY [taskid]
----------------结果----------------------------
/*
taskid iscall ismsg isfax isemail
----------- ------ ----- ----- -------
12 1/3 0/3 0/3 0/3
23 1/3 0/3 0/3 0/3
*/
