SqlServer根据类型分组后过滤条件求和、计算平均值

 SELECT 归属类别,
sum(CASE 资费类型 WHEN 'N' THEN 办理量 ELSE 0 end ) AS xx办理量
,convert(varchar,Convert(decimal(18,2),(sum(CASE 资费类型 WHEN 'N' THEN 办理量 ELSE 0 end )/(select sum(CASE 资费类型 WHEN 'N' THEN 办理量 ELSE 0 end ) AS 新业务办理量   from  tab1))*100))+'%'   AS  xx率1
,sum(CASE 资费类型 WHEN 'N' THEN 价值 ELSE 0 end ) AS xx月租
,Convert(decimal(18,2),sum(CASE 资费类型 WHEN 'N' THEN 价值 ELSE 0 end )/sum(CASE 资费类型 WHEN 'N' THEN 办理量 ELSE 0 end )) as  xx平均月租
,sum(CASE 资费类型 WHEN 'H' THEN 办理量 ELSE 0 end ) AS xx办理量
,convert(varchar,Convert(decimal(18,2),(sum(CASE 资费类型 WHEN 'H' THEN 办理量 ELSE 0 end )/(select sum(CASE 资费类型 WHEN 'H' THEN 办理量 ELSE 0 end ) AS 宽带办理量   from  tab1))*100))+'%'  AS xx率2
,sum(CASE 资费类型 WHEN 'H' THEN 价值 ELSE 0 end ) AS xx月租 
,Convert(decimal(18,2),sum(CASE 资费类型 WHEN 'H' THEN 价值 ELSE 0 end )/sum(CASE 资费类型 WHEN 'H' THEN 办理量 ELSE 0 end )) as xx平均月租
,sum(CASE 资费类型 WHEN 'C' THEN 办理量 ELSE 0 end ) AS xx办理量
,convert(varchar,Convert(decimal(18,2),(sum(CASE 资费类型 WHEN 'C' THEN 办理量 ELSE 0 end )/(select sum(CASE 资费类型 WHEN 'C' THEN 办理量 ELSE 0 end ) AS 号卡办理量   from  tab1))*100))+'%'   AS xx率3
,sum(CASE 资费类型 WHEN 'C' THEN 价值 ELSE 0 end ) AS xx月租 
,Convert(decimal(18,2),sum(CASE 资费类型 WHEN 'C' THEN 价值 ELSE 0 end )/sum(CASE 资费类型 WHEN 'C' THEN 办理量 ELSE 0 end )) as  xx平均月租
,sum(CASE 资费类型 WHEN 'B' THEN 办理量 ELSE 0 end ) AS xx办理量 
,convert(varchar,Convert(decimal(18,2),(sum(CASE 资费类型 WHEN 'B' THEN 办理量 ELSE 0 end )/(select sum(CASE 资费类型 WHEN 'B' THEN 办理量 ELSE 0 end ) AS 政企办理量   from  tab1))*100))+'%'   AS xx率4
,sum(CASE 资费类型 WHEN 'B' THEN 价值 ELSE 0 end ) AS xx月租  
,Convert(decimal(18,2),sum(CASE 资费类型 WHEN 'B' THEN 价值 ELSE 0 end )/sum(CASE 资费类型 WHEN 'B' THEN 办理量 ELSE 0 end )) as xx平均月租
FROM tab1 GROUP BY 归属类别