title: when duplicate values found then
body: <p>I want to have a query that selects all duplicate values in a column. If those value meet the conditions then I'd like the query to return only those values.</p>
  
  <pre><code>Class    Student_ID  Location
  Biology     511         4A
  Biology     512         15B
  Biology     513         15B
  English     514         6A
  Biology     521         6A
  Spanish     522         6A
  Spanish     523         15B
  Chemistry   524         4A
  English     531         15B
  Biology     532         4A
  Chemistry   534         4A
  </code></pre>
  
  <p>Select all duplicate values in the class column and if among those values there is location in both 4A and 15B then assign 1.</p>
  
  <pre><code>CASE WHEN count(class) &gt; 1 AND (Location = '4A' AND Location = '15B') THEN 1
   ELSE 0 END
  </code></pre>
  
  <p>what is most important is how to select duplicate values as a group and then look at the condition (location must be 4A and 15B). So the query must first group the duplicated values from the class column and then see if within the group the values meet the condition of location. So for example we first group the class column we get 5x biology this is then seen as a group and then within this group if there exist one row with location 4A AND one row with location 15B then and only then assign value 1 for biology. Almost all the values in class column have duplicates.</p>
  
  <p><strong>Desired Output</strong></p>
  
  <pre><code>Class       Location
  Biology     1
  Chemistry   0
  English     0
  Spanish     0
  </code></pre>