title: teradata sql multiple occurrences as single value
body: <p>I am fighting with a query where I need to retrieve a list of IDs and a value.</p>
  
  <p>Then I need to cast a new value for those IDs that have more than one value.</p>
  
  <p>For example:</p>
  
  <pre><code>ID          COLOR
  1           BLUE
  1           GREEN
  2           ORANGE
  </code></pre>
  
  <p>What I want to return is like this:</p>
  
  <pre><code>ID          COLOR
  1           MULTIPLE
  2           ORANGE
  </code></pre>
  
  <p>So far, I've used ROW_NUMBER() and OVER(PARTITION BY) to retrieve all the results and number them.</p>
  
  <p>But then I get stuck going in circles.</p>
  
  <p>I can limit by those IDs that have multiple occurrences (where the row count >=2) and I get those IDs that need to be cast as "MULTIPLE".</p>
  
  <p>BUT: Those same values are also included if I limit to IDs that have only one value (=1), because the multiple value IDs also have a row number 1. I wind up with an invalid result because I'm counting the multiple-value-IDs twice, kind of like this:</p>
  
  <pre><code>ID         COLOR
  1          BLUE
  1          MULTIPLE
  2          ORANGE
  </code></pre>
  
  <p>So the question is: how can I get all the IDs with multiple values, separate from those that only have one value?</p>