Case concept...Sql服务器查询

Question

我正在尝试下面的方法。我的内部查询运行得很好。但是当我尝试整个查询时，我得到了一个错误。请提供您的输入。

select 
   x.TeamProjectProjectNodeName,
   x.TestPlanName, 
   (case when x.ResultOutcome in ('Aborted','Error','Failed','Inconclusive','Timeout','Blocked') 
     then sum(count(x.ResultOutcome)) else 0
     as "Failed"  
     when x.ResultOutcome in ('Passed') 
     then sum(count(x.ResultOutcome)) else 0
     as "Passed" 
     when x.ResultOutcome in ('Not Executed') 
     then sum(count(x.ResultOutcome)) else 0
     as "Not Run"
     END
    )
from (select 
         TeamProjectProjectNodeName,
         TestPlanName,
         ResultOutcome, 
         count(*) as Total 
      from [Tfs_Warehouse].[dbo].[TestResultView] 
      where TestPlanName IS NOT NULL
      GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutcome
     ) x
order by TeamProjectProjectNodeName asc, TestPlanName asc 

这就是我从内部查询中得到的-

TeamProjectProjectNodeName  TestPlanName    ResultOutcome   Total
ACATS                       Test Automation Aborted            26
ACATS                       Test Automation Failed             61

这是我正在寻找的最终结果格式-

TeamProjectProjectNodeName  TestPlanName    Failed  Passed    Not_Run
ACATS                       Test Automation 87     0        0

Answer 1

嵌套聚合(即(sum(count))) )是无效语法。根据您现有的查询，您似乎正在尝试如下所示的数据透视：

select 
   x.TeamProjectProjectNodeName,
   x.TestPlanName, 
   sum(case when x.ResultOutcome in ('Aborted','Error','Failed','Inconclusive','Timeout','Blocked') then 1 else 0 end) as Failed,
   sum(case when x.ResultOutcome = 'Passed' then 1 else 0 end) as Passed,
   sum(case when x.ResultOutcome = 'Not Executed' then 1 else 0 end) as NotRun
from 
(
   select 
       TeamProjectProjectNodeName,
       TestPlanName,
       ResultOutcome, 
       count(*) as Total 
   from [Tfs_Warehouse].[dbo].[TestResultView] 
   where TestPlanName IS NOT NULL
   GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutcome
) x
group by x.TeamProjectProjectNodeName, x.TestPlanName
order by TeamProjectProjectNodeName asc, TestPlanName asc;

您应该能够通过使用以下命令来合并此代码：

select 
   TeamProjectProjectNodeName,
   TestPlanName,
   sum(case when ResultOutcome in ('Aborted','Error','Failed','Inconclusive','Timeout','Blocked') then 1 else 0 end) as Failed,
   sum(case when ResultOutcome = 'Passed' then 1 else 0 end) as Passed,
   sum(case when ResultOutcome = 'Not Executed' then 1 else 0 end) as NotRun
from [Tfs_Warehouse].[dbo].[TestResultView] 
where TestPlanName IS NOT NULL
GROUP BY TeamProjectProjectNodeName, TestPlanName
order by TeamProjectProjectNodeName asc, TestPlanName asc;