分组统计数据

;WITH a AS
(
SELECT CONVERT(VARCHAR(10),StartTime,120) StartTime,
CASE WHEN OrganID LIKE '0226%' THEN '热线' ELSE '非热线' END AS deptname,
SUM(CASE  WHEN BillableSeconds>=0 THEN 1 ELSE 0 END )as a,--总进线,
SUM(CASE WHEN BillableSeconds>=3 THEN 1 ELSE 0 END ) AS b,--'3秒以上进线',
SUM(CASE WHEN BillableSeconds=0 THEN 1 ELSE 0 END) AS c-- 未接通
 FROM dbo.bmdcallcenterrecord b(NOLOCK)
JOIN BRM_LVJIAN.dbo.mdDepartment md(NOLOCK) ON b.DepartmentID=md.ID
WHERE did LIKE '89%'
AND CallType='1'
AND StartTime>='2014-6-1'
GROUP BY CONVERT(VARCHAR(10),StartTime,120),OrganID
)
SELECT StartTime,deptname,SUM(a) AS '总进线',SUM(b) AS '3秒以上进线',SUM(c) AS '未接通' FROM a
GROUP BY StartTime,deptname
ORDER BY deptname,StartTime

 

 

---按时间，部门来分组

2014-06-06 非热线 42 6 35
2014-06-07 非热线 53 12 41
2014-06-08 非热线 88 0 88
2014-06-09 非热线 90 13 76
2014-06-10 非热线 54 11 42
2014-06-11 非热线 49 12 37
2014-06-12 非热线 39 10 29
2014-06-13 非热线 49 17 32
2014-06-14 非热线 36 6 30

 

 

----不管按什么来分组，就是group by 分组分类，后面sum.