title: Need help understanding why COUNT DISTINCT is returning an odd result
body: <p>I have a table t1 that contains several fields one being INC_ID.  I was looking to identify and remove the records in t1 with duplicate values of INC_ID.  </p>
  
  <p>For background, the other columns in t1 may be the same among records with duplicates of INC_ID and they may not.  e.g.</p>
  
  <p><strong>INC_ID, VAL1, VAL2</strong></p>
  
  <p>144345, Red7, ABC</p>
  
  <p>144345, Red7, ABC</p>
  
  <p>144346, Grn2, DEF</p>
  
  <p>144346, Grn2, DEF</p>
  
  <hr>
  
  <p>When I execute </p>
  
  <pre><code>SELECT COUNT(*)
  FROM t1
  </code></pre>
  
  <p>It returns 10,000 as expected.</p>
  
  <p>When I execute </p>
  
  <pre><code>SELECT COUNT(*)
  FROM t1
  WHERE INC_ID IN
  (SELECT INC_ID
  FROM t1
  GROUP BY INC_ID
  HAVING COUNT(*)=1)
  </code></pre>
  
  <p>It returns 9,974.  I am assuming this means I have 9,974 records with a unique INC_ID</p>
  
  <p>When I execute</p>
  
  <pre><code>SELECT COUNT(*)
  FROM t1
  WHERE INC_ID IN
  (SELECT INC_ID
  FROM t1
  GROUP BY INC_ID
  HAVING COUNT(*)&gt;1)
  </code></pre>
  
  <p>I get 26 as expected.  An additional check shows that there are indeed 26 records with duplicate INC_ID, there being only 13 unique INC_IDs in the 26 records.</p>
  
  <p>When I run </p>
  
  <pre><code>SELECT COUNT(DISTINCT INC_ID)
  FROM t1
  </code></pre>
  
  <p>I get 9,987.  Not understanding this I thought maybe it was calling the record distinct due to other columns being different even when the INC_IDs were the same.</p>
  
  <p>Then I tried </p>
  
  <pre><code>SELECT COUNT(DISTINCT INC_ID)
  FROM
  (SELECT INC_ID
  FROM t1)
  </code></pre>
  
  <p>but that still returns 9,987.  Clearly I have some flaw in my understanding of how DISTINCT works.  Can someone explain the why of this to me?  Thanks in advance for your time.</p>