如何通过案例函数汇总

Question

我有一些有效的代码，但是我需要计算一些DTA，并且似乎无法使其起作用。

请参见下面的代码，以及带有评论不起作用的ON。

 SELECT 
   siteid,
   linenum,
   worktype,
   COUNT(CASE WHEN status = 'APPR' THEN 1 ELSE NULL END)   AS [Approved],
   COUNT(CASE WHEN status = 'review' THEN 1 ELSE NULL END)   AS [Review],
   COUNT(CASE WHEN status = 'wmatl' THEN 1 ELSE NULL END)   AS [WaitMatl],
   COUNT(CASE WHEN status = 'comp' THEN 1 ELSE NULL END)   AS [Complete],
   COUNT(CASE WHEN status = 'incomp' THEN 1 ELSE NULL END)   AS [InComplete],
   COUNT(CASE WHEN status = 'closed' THEN 1 ELSE NULL END)   AS [Closed],
   COUNT(CASE WHEN status not in ('appr','wmatl') THEN 1 ELSE NULL END) AS [All_Completed],
   Count (*) as allrecords,
   /* The below divide by does not work */
   COUNT(CASE WHEN status not in ('appr','wmatl') THEN 1 ELSE null END)  / Count (*) as Completion_Ratio
  FROM workorder
  WHERE (siteid in ('p202','p203','p201')) AND (worktype in   ('mpm','ppm','tspm')) AND (istask ='0') 
AND (historyflag ='0')  AND (woclass = 'workorder') --AND (status not in ('comp','closed','review','incomp'))
AND (assetnum is not null) AND (maintby not in ('ms','ed')) 
AND targcompdate < DATEADD(mm,DATEDIFF(mm,0,GETDATE())-0,0)
GROUP BY siteid,linenum,worktype
Order by siteid, linenum,worktype

Answer 1

尝试以下：

select 
    siteid,
    linenum,
    worktype,
    sum(case when status = 'appr'   then 1 else null end) as [approved],
    sum(case when status = 'review' then 1 else null end) as [review],
    sum(case when status = 'wmatl'  then 1 else null end) as [waitmatl],
    sum(case when status = 'comp'   then 1 else null end) as [complete],
    sum(case when status = 'incomp' then 1 else null end) as [incomplete],
    sum(case when status = 'closed' then 1 else null end) as [closed],
    sum(case when status not in ('appr','wmatl') then 1 else null end) as [all_completed],
    count (*) as allrecords,
    sum(case when status not in ('appr','wmatl') then 1 else null end)  / count (*) as completion_ratio
  from workorder
  where (siteid in ('p202','p203','p201')) 
    and (worktype in   ('mpm','ppm','tspm')) 
    and (istask ='0') 
    and (historyflag ='0') 
    and (woclass = 'workorder') 
    --and (status not in ('comp','closed','review','incomp'))
    and (assetnum is not null)
    and (maintby not in ('ms','ed')) 
    and targcompdate < dateadd(mm,datediff(mm,0,getdate())-0,0)
  group by siteid, linenum, worktype
  order by siteid, linenum, worktype