title: SQL count and distinct and partition
body: <p>I am trying out some examples with analytical functions and i have created a sql fiddle to understand a count distinct over partition by clause.This is my <a href="http://sqlfiddle.com/#!4/510f7/3" rel="nofollow">sqlfiddle</a>.  </p>
  
  <pre><code>create table dummy (value1 varchar2(10),value2 varchar2(10));
  
  insert into dummy values ('abc','abc1');
  insert into dummy values ('abc','abc1');
  insert into dummy values ('abc','abc2');
  insert into dummy values ('def','abc1');
  insert into dummy values ('ghi','abc2');
  insert into dummy values ('xyz','abc3');
  insert into dummy values ('xyz','abc3');
  
  select value1,
         value2,
         count(distinct value2) over (partition by value1) as ValCount
  from dummy
  </code></pre>
  
  <p>If you look at the result set, i would expect valcount as 1 for the third row but instead its 2 and am not sure why that's the case.</p>