sql-server - 需要帮助来计算分组

Question

我有一个包含以下列的 SQL 表

---

表一

Site  Det  FailFlag  Comments  ModifiedDate  ModifiedBy    
ABT   A01     F      Dfasdf    10/11/2011    Anna
ABT   A01     F      dsfsdf    15/12/2012    Mand
ABT   A01            Rds       30/03/2011    Tim
ABT   A01            GHDs      02/12/2012    Andy
ABT   A02     F      dfd       09/05/2012    Anna
ABT   A02            sdada     11/02/2013    Kathy
ABT   A02            Dfg       15/05/2011    Rob
TRG   A01            sdasd     16/04/2010    Richard
TRG   K05            jksdh     23/04/2012    Mark
KLD   KO5     F      sd        18/05/2013    Jim
KLD   KO5            dsfsd     10/03/2012    James
KLD   KO5            sdsd      12/05/2011    Luther

我需要将以下内容作为输出:

Site  Det  Count  CountOfFailures
ABT   A01     4      2
ABT   A02     3      1
TRG   A01     1      0
TRG   K05     1      0
KLD   KO5     3      1

我知道我可以按 Site 和 Det 分组并获得计数但无法获得失败次数的计数

Select Site,Det, Count(*) AS [Count] from Table1  
group by Site,Det  order by Count

同样需要帮助。

Answer 1

试试这个:-

  Select site,det,
  FlagCount = sum(case when FailFlag='F' then 1 else 0 ENd) 
  from yourTable
  group by Site,Det
  order by Site

输出

  +------+-----+-----------+
  | site | det | FlagCount |
  +------+-----+-----------+
  | ABT  | A01 |         2 |
  | ABT  | A02 |         1 |
  | KLD  | KO5 |         1 |
  | TRG  | A01 |         0 |
  | TRG  | K05 |         0 |
  +------+-----+-----------+