sql - 每日数据和总数据

Question

请有人帮我写一个查询，从下面的查询中提取 Daywise 数据和总数据。

Select TCl.CircleCode Location,
COUNT(CASE 
 WHEN HostResponse=0 THEN 'Failure' 
 End) Failure,
 Count(Case
 WHEN HostResponse=1 THEN 'Success'
  END) 'Success'
from [dbo].[TBL_DTL_HOST] Host Join [TBL_DTL_CALLACTIVITY_REALTIME_230818] TCL On
Host.CallID = TCL.CallID
Where HostMethod='Caller Validation'
and CallDateTime Between '2018-07-01 00:00:00' and '2018-07-24 23:59:59'
Group by  TCl.CircleCode

标签： sqlsql-server

Answer 1

SELECT
   TCl.CircleCode Location
 , Count(CASE
             WHEN HostResponse = 0
             THEN 'Failure'
         END) Failure
 , Count(CASE
             WHEN HostResponse = 1
             THEN 'Success'
         END) 'Success'
 , Cast(CallDateTime AS DATE) CallDate
FROM
   dbo.TBL_DTL_HOST Host
   JOIN TBL_DTL_CALLACTIVITY_REALTIME_230818 TCL ON Host.CallID = TCL.CallID
WHERE HostMethod = 'Caller Validation'
      AND CallDateTime BETWEEN '2018-07-01 00:00:00' AND '2018-07-24 23:59:59'
GROUP BY
   Cast(CallDateTime AS DATE)
 , TCl.CircleCode;