title: TSQL define group statuses based on group row statuses
body: <p>Hi any ideas how i can define group statuses based on group row statuses or what I should be googling?
  In the below Group Status is what I'm after. Thanks</p>
  
  <pre><code>Group  Row Status  Group Status
  A      Good        Good
  B      Good        Neutral
  B      Neutral     Neutral
  B      Bad         Neutral
  C      Bad         Bad
  D      Good        Error
  D      Bad         Error
  D      Error       Error
  </code></pre>
  
  <p>There is an error in my table above, Good+Neutral+Bad=Bad and C should be Neutral=Neutral so this will cause some confusion with answers from @forpas and @Gordon Linoff<br>
  I have added another table below to better show cases/rules <br>
  Any mix with an Error = Error <br>
  Any mix of Good Neutral Bad = Bad <br>
  Any distinct Status = that Status</p>
  
  <pre><code>Group   Row Status  Group Status
  A       Good        Bad
  A       Bad         Bad
  A       Neutral     Bad
  B       Good        Bad
  B       Bad         Bad
  C       Good        Bad
  C       Neutral     Bad
  D       Bad         Bad
  D       Neutral     Bad
  E       Good        Good
  F       Bad         Bad
  G       Neutral     Neutral
  H       Error       Error
  I       Good        Error
  I       Bad         Error
  I       Neutral     Error
  I       Error       Error
  J       Good        Error
  J       Bad         Error
  J       Error       Error
  K       Good        Error
  K       Neutral     Error
  K       Error       Error
  L       Bad         Error
  L       Neutral     Error
  L       Error       Error
  M       Good        Error
  M       Error       Error
  N       Bad         Error
  N       Error       Error
  O       Neutral     Error
  O       Error       Error
  </code></pre>
  
  <p>I was able to get result i wanted using @forpas method. I think this should cover all my cases.</p>
  
  <pre><code>with cte as (
    select
      [Group],
      sum(case [Row Status] when 'Good' then 1 else 0 end) good,
      sum(case [Row Status] when 'Neutral' then 1 else 0 end) neutral,
      sum(case [Row Status] when 'Bad' then 1 else 0 end) bad,
      sum(case [Row Status] when 'Error' then 1 else 0 end) error
    from [Test].[dbo].[GroupStatus]
    group by [Group]
  )
  select 
    t.*,
    case 
      when c.error &gt; 0 then 'Error'
      when c.good * c.neutral * c.bad &gt; 0 then 'Bad'
      when c.good * c.neutral &gt; 0 then 'Bad'
      when c.bad * c.neutral &gt; 0 then 'Bad'
      when c.good * c.bad &gt; 0 then 'Bad'
      when c.good &gt; 0 then 'Good'
      when c.bad &gt; 0 then 'Bad'
      when c.neutral &gt; 0 then 'Neutral'
      else 'Error'
    end [Group Status] 
  from [Test].[dbo].[GroupStatus] t inner join cte c
  on c.[Group] = t.[Group]
  </code></pre>