title: Count function within a Case When condition
body: <p>Could anyone explain to me the difference between the SQL statements below? I can see there is a difference but I can't nail down the exact conditions which can make them produce different results. By the way, I'm thinking the <code>distinct</code> clause doesn't make a difference on the <code>user.id</code> field since all the ids are already unique. The purpose of the query is to count the number of unique (non-empty) last names. If the last name is empty, then count as unique.</p>
  
  <p>I suppose the general case for this problem would be the use of an aggregate function within a case-when statement.</p>
  
  <p>Count within Case-When:</p>
  
  <pre><code>SELECT 
      (case when (substr(u.name,40,40) &lt;&gt; '                                        ')
          then count(distinct(substr(u.name,40,40)))
          else count(u.id) 
      end) as "LAST_NAME",
  FROM 
      users u
  GROUP BY
      substr(u.name,40,40)
  </code></pre>
  
  <p>Case-When within Count:</p>
  
  <pre><code>SELECT 
      count (distinct case when (substr(u.name,40,40) &lt;&gt; '                                        ')
          then substr(u.name,40,40)
          else to_char(u.id)
      end) as "LAST_NAME",
  FROM 
      users u
  GROUP BY
      substr(u.name,40,40)
  </code></pre>