title: Groupby Certain Columns to Generate Values
body: <p>Based on certain key columns (i.e. <code>Location</code>, <code>Sublocation</code>, and <code>Area</code>), how can I groupby those columns to have standard values? In the below example, the only rule is, grouping by <code>Location</code>, <code>Sublocation</code> and <code>Area</code>, if the value of <code>Excellent</code> is seen two or more times (not a majority because there may be more rows for other locations) in column <code>Initial Result</code> for the grouped scenario, then the <code>Final Result</code> for the grouped columns should be <code>PASS</code>. If that is not the case, then <code>FAIL</code>.
  This is only a portion of the larger dataset where multiple location, sublocation, and area level combinations exist.</p>
  <p>Input Example</p>
  <pre><code>Location Sublocation  Initial Results
  School   Cafeteria    Excellent       
  School   Cafeteria    Poor            
  School   Cafeteria    Excellent       
  School   Auditorium   Poor
  School   Auditorium   Poor
  School   Auditorium   Excellent
  </code></pre>
  <p>Output</p>
  <pre><code>Location Sublocation  Initial Results  Final Results
  School   Cafeteria    Excellent        PASS
  School   Cafeteria    Poor             PASS
  School   Cafeteria    Excellent        PASS
  School   Auditorium   Poor             FAIL
  School   Auditorium   Poor             FAIL
  School   Auditorium   Excellent        FAIL
  </code></pre>
  <p>I am thinking I can try to do a count of the value <code>Excellent</code> grouping by the above criteria. After I do a count, then I can simply say if it's 2 or more then create another column and mark it as pass or fail? I think this is requiring more steps then needed though...</p>
  <p>Code attempt</p>
  <pre><code>select a.*, count(case when 'initial results' = 'Excellent' &gt; 2 then 'PASS' else ' 'FAIL') end as 'Final Result'
  from MyTable a
  group by location, sublocation, area
  </code></pre>
  <p>I am getting numerous error messages and I think it's because I probably need to create a separate count query?</p>