title: Find number of non-unique column values within a group where a second column value is all the same
body: <p>I have two tables.</p>
  <pre><code>Table 1:
  id_a, 
  id_b, 
  id_t
  </code></pre>
  <pre><code>Table 2: 
  id_t, 
  name
  </code></pre>
  <p>If Table 2 name starts with <code>a</code>, I need to find out of anything
  with matching <code>id_ts</code> also have matching <code>id_as</code>.
  If Table 2 name starts with <code>b</code>, I need to find out of any row
  with matching id_ts also have matching <code>id_bs</code>.</p>
  <p>I need to know how many times these matches occur.</p>
  <p>Table 1</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id_a</th>
  <th>id_b</th>
  <th>id_t</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>0</td>
  <td>123</td>
  </tr>
  <tr>
  <td>1</td>
  <td>0</td>
  <td>123</td>
  </tr>
  <tr>
  <td>2</td>
  <td>0</td>
  <td>123</td>
  </tr>
  <tr>
  <td>0</td>
  <td>4</td>
  <td>456</td>
  </tr>
  <tr>
  <td>0</td>
  <td>4</td>
  <td>456</td>
  </tr>
  <tr>
  <td>0</td>
  <td>5</td>
  <td>456</td>
  </tr>
  <tr>
  <td>0</td>
  <td>5</td>
  <td>456</td>
  </tr>
  <tr>
  <td>0</td>
  <td>5</td>
  <td>456</td>
  </tr>
  <tr>
  <td>0</td>
  <td>6</td>
  <td>456</td>
  </tr>
  <tr>
  <td>0</td>
  <td>7</td>
  <td>456</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table 2</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id_t</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>123</td>
  <td>aaq</td>
  </tr>
  <tr>
  <td>456</td>
  <td>bws</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So in this example, I want to see a result like</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id_t</th>
  <th>name</th>
  <th>num_non_unique</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>123</td>
  <td>aaq</td>
  <td>1</td>
  </tr>
  <tr>
  <td>456</td>
  <td>bws</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My current code is this:</p>
  <pre><code>SELECT
      t2.id_t, t2.name, count(t1.*) AS num_non_unique
  FROM
      Table 2 AS t2
      JOIN Table 1 as t1 ON t2.id_t = t1.id_t
  WHERE
          (t2.name like 'a%' and t1.id_a in (SELECT id_a FROM t1 GROUP BY id_a, id_t HAVING count(*) &gt; 1))
      OR  (t2.name like 'b%' AND t1.id_b IN (SELECT id_b FROM t1 GROUP BY id_b, id_t HAVING count(*) &gt; 1))
  GROUP BY t1.name, t1.id_t
  </code></pre>
  <p>This doesn't currently give me the results I want.
  With this code I seem to get the count of all available rows for id_b, and 1 + non_uniques for id_a (so with one non-unique, the value is 2, otherwise the column has a 1).</p>
  <p>Any help is appreciated!</p>