title: Counting conditional matches in SQL
body: <p><strong>Problem:</strong></p>
  
  <p>Find the number of matching records in the Person table where First and last names are the same, or, the SSN is the same.</p>
  
  <p><em><strong>GIVENS</em></strong></p>
  
  <p><strong>TABLE STRUCTURE:</strong> Person</p>
  
  <pre><code>FirstName
  LastName
  SSN
  RecordId
  </code></pre>
  
  <p><strong>DATA:</strong> in Person table</p>
  
  <pre><code>╔═══════════╦══════════╦═════════════╦══════════╗
  ║ FIRSTNAME ║ LASTNAME ║     SSN     ║ RECORDID ║
  ╠═══════════╬══════════╬═════════════╬══════════╣
  ║ Bob       ║ Smith    ║ 111-11-1111 ║        1 ║
  ║ Robert    ║ Smyth    ║ 111-11-1111 ║        2 ║
  ║ Bob       ║ Smith    ║ 222-22-2222 ║        3 ║
  ║ Tom       ║ Gunn     ║ 333-33-3333 ║        4 ║
  ║ Jim       ║ Gunn     ║ 444-44-4444 ║        5 ║
  ║ Jimmy     ║ Gunn     ║ 444-44-4444 ║        6 ║
  ║ Alice     ║ Jones    ║ 555-55-5555 ║        7 ║
  ║ Alice     ║ Jones    ║ 666-66-6666 ║        8 ║
  ╚═══════════╩══════════╩═════════════╩══════════╝
  </code></pre>
  
  <p><strong>DESIRED RESULTS:</strong></p>
  
  <pre><code>╔════════════╦═══════════╦══════════╦═════════════╦══════════╗
  ║ MATCHCOUNT ║ FIRSTNAME ║ LASTNAME ║     SSN     ║ RECORDID ║
  ╠════════════╬═══════════╬══════════╬═════════════╬══════════╣
  ║          3 ║ Bob       ║ Smith    ║ 111-11-1111 ║        1 ║
  ║          2 ║ Robert    ║ Smyth    ║ 111-11-1111 ║        2 ║
  ║          2 ║ Bob       ║ Smith    ║ 222-22-2222 ║        3 ║
  ║          1 ║ Tom       ║ Gunn     ║ 333-33-3333 ║        4 ║
  ║          2 ║ Jim       ║ Gunn     ║ 444-44-4444 ║        5 ║
  ║          2 ║ Jimmy     ║ Gunn     ║ 444-44-4444 ║        6 ║
  ║          2 ║ Alice     ║ Jones    ║ 555-55-5555 ║        7 ║
  ║          2 ║ Alice     ║ Jones    ║ 666-66-6666 ║        8 ║
  ╚════════════╩═══════════╩══════════╩═════════════╩══════════╝
  </code></pre>
  
  <p><strong>What I have tried</strong></p>
  
  <p>I have tried using both GROUP BY  and an LEFT self JOIN to solve this problem.</p>
  
  <p>GROUP BY fails because there is no way to conditionally group.</p>
  
  <p>The LEFT self JOIN fails because it returns roughly the square of the number of matches.</p>
  
  <p>I would post the code of my attempts, but my real tables have a large number of columns and  is actually far more complex and would almost certainly obscure the issue.</p>