title: Count distinct and Null value is eliminated by an aggregate
body: <p>I'm using SQL Server 2005. With the query below (simplified from my real query):</p>
  
  <pre><code>select a,count(distinct b),sum(a) from 
  (select 1 a,1 b union all
  select 2,2 union all
  select 2,null union all
  select 3,3 union all
  select 3,null union all
  select 3,null) a
  group by a
  </code></pre>
  
  <p>Is there any way to do a count distinct without getting</p>
  
  <p>"Warning: Null value is eliminated by an aggregate or other SET operation."</p>
  
  <p>Here are the alternatives I can think of:</p>
  
  <ol>
  <li>Turning ANSI_WARNINGS off</li>
  <li><p>Separating into two queries, one with count distinct and a where clause to eliminate nulls, one with the sum:</p>
  
  <pre><code>select t1.a, t1.countdistinctb, t2.suma from
  (
      select a,count(distinct b) countdistinctb from 
      (
          select 1 a,1 b union all
          select 2,2 union all
          select 2,null union all
          select 3,3 union all
          select 3,null union all
          select 3,null
      ) a
      where a.b is not null
      group by a
  ) t1
  left join
  (
      select a,sum(a) suma from 
      (
          select 1 a,1 b union all
          select 2,2 union all
          select 2,null union all
          select 3,3 union all
          select 3,null union all
          select 3,null
      ) a
      group by a
  ) t2 on t1.a=t2.a
  </code></pre></li>
  <li><p>Ignore the warning in the client</p></li>
  </ol>
  
  <p>Is there a better way to do this? I'll probably go down route 2, but don't like the code duplication.</p>