title: How can I reduce my dataset by omitting a value within a group
body: <p>I was looking for some assistance in regards to my query.  I need to create a new table that contains a subset of data from the old table.  The important attributes used to do this are the groupid, and the category.  The category contains string data that has both letters and numbers.  Here is a sample of what my data looks like:</p>
  
  <pre><code>---------------------------------------------
  | groupid    | category     |  test_results |
  ---------------------------------------------
  | 001        |  red16tall   |               |
  | 001        |  blue13small |
  | 002        |  green16small|
  | 002        |  blue16small |
  | 003        |  yellow3tall |
  | 003        |              |
  | 004        | orange16tall |
  | 004        | blue3tall    |
  | 005        | red16short   |
  | 005        |              |
  | 006        | blue3short   |  red16big  |
  | 006        | green16flat  |            |
  | 007        |              | black16fat |
  | 007        | orange05big  |            |
  ------------------------------------------
  </code></pre>
  
  <p>What I would like to do is omit any groups where BOTH records have a 16 in them, and keep everything else.  Citing the above example, this is what I would like the new table to look like:</p>
  
  <pre><code>-----------------------------
  | groupid    | category     |
  -----------------------------
  | 001        |  red16tall   |
  | 001        |  blue13small |
  | 003        |  yellow3tall |
  | 003        |              |
  | 004        | orange16tall |
  | 004        | blue3tall    |
  | 005        | red16short   |
  | 005        |              |
  | 007        |              | black16fat |
  | 007        | orange05big  |            |
  ------------------------------------------    
  </code></pre>
  
  <p>This is the query I attempted to use, but this omits everything that has a 16 in it:</p>
  
  <pre><code>create table mytable as
  select *
  from old_table t1
  where not exists (
      select 1 from old_table  t2
      where t1.groupid = t2.groupid
      and t2.catagory like '%16%'  );
  </code></pre>
  
  <p>Can anyone help me refine this code, or does anyone know of a better way to do this?  Keep in mind that I would like to keep all attributes within the dataset, I just want to get rid of groups where both records have a 16.  Thank you.</p>
  
  <p>edit: the test_results field was added to the original table, as well as the last 4 records as an example.  how can I exclude records from group_id 006 in addition to the original query?  there is a %16% in both the category as well as the test_results fields. I'd also like to keep records where test results is in 16, but category is null.</p>