title: Using GROUP BY clause to replace dissimilar rows with single value
body: <p>I am trying to do something like (psuedo - of course): </p>
  
  <pre><code>SELECT 
    city,
    CASE WHEN COUNT( [group] ) &gt; 1 THEN 'Multiple' ELSE [group] END  AS Type 
  FROM 
    offices
  GROUP BY
    city
  </code></pre>
  
  <p>Where offices contains rows like:</p>
  
  <pre><code>ID |   group   |  city
  ----------------------
  1  |    'A'    | 'Houston'
  2  |    'B'    | 'Houston'
  3  |    'C'    | 'Houston'
  4  |    'S'    | 'Boston'
  5  |    'R'    | 'Detroit'
  </code></pre>
  
  <p>and result would look something like:</p>
  
  <pre><code>   city   | group
  --------------------
   'Houston'| 'Multiple'
   'Boston' |   'S'
   'Detroit'|   'R'
  </code></pre>
  
  <p>I know you can do:</p>
  
  <pre><code>SELECT 
    City,
    CASE WHEN COUNT([group]) &gt; 1 THEN 
        'Multiple'
    ELSE 
         ( SELECT [group] FROM test WHERE t.City = city )
    END AS CGroup
  
  FROM 
     test t
  GROUP BY 
     City
  </code></pre>
  
  <p>I thought this should be simpler.<br>
  Something without a sub query?</p>