title: counting null values in sql with where and group by clause
body: <p>Here is the sample data for test table I have</p>
  
  <pre><code>[childId] [parentId] [present]
  1         10         0
  2         11         1
  3         NULL       0
  4         NULL       0
  NULL      10         0
  </code></pre>
  
  <p>Now, for all the parentIds (including those with NULL) I want to count all the present childIds</p>
  
  <pre><code>select parentId, count(childId) as nbr
  from TestTable
  where present=1 or parentId is NULL
  group by parentId
  </code></pre>
  
  <p>The result I get is   </p>
  
  <pre><code>parentId    nbr
  NULL        2
  11          1
  </code></pre>
  
  <p>Same count number (<code>nbr</code>) I get for both present=1 and present=0. It seems that I cannot impose a condition for the <code>NULL</code> grouped value.<br>
  What's the mistake I'm making and what's the solution for the query I want to make?</p>
  
  <hr>
  
  <p><strong>UPDATE</strong>:  </p>
  
  <p>Since the test case I gave is not representative, until I recreate the real situation in better way, I'll try to explain the problem I'm facing.</p>
  
  <p>I created a view with full outer join, so I have some records having <code>NULL</code> for <code>childId</code> and some records having <code>NULL</code> for <code>parentId</code>.<br>
  Now, I want to list all of the <code>parentId</code> values (both <code>NULL</code> and non <code>NULL</code>) and for each of them, count all the records that, for example, have <code>present=1</code>.
  If I use <code>where present=1</code> condition, it eliminates NULL values from result set, i.e. I won't have result record <code>NULL|x</code> for <code>parentId|nbr</code>.<br>
   I solved this with <code>union all</code>. In first select I have <code>where present=1</code> and in second select I have <code>where present=1 and parentId is NULL</code>.   </p>
  
  <pre><code>select parentId, count(childId) as nbr
  from TestTable
  where present=0
  group by parentId
  
  union all
  
  select parentId, count(childId) as nbr
  from TestTable
  where present=0 and parentId is NULL
  group by parentId
  </code></pre>
  
  <p>The result I get is</p>
  
  <pre><code>    [parentId]  [nbr]
      NULL        2
      10          1
      NULL        2
  </code></pre>
  
  <p>The only problem with this (besides duplicated record for <code>parentId=NULL</code>), is that if there are no records with <code>parentId=NULL</code> and <code>present=1</code>, in result I won't have record with <code>NULL | 0</code> for <code>parentId|nbr</code> and I want to list ALL parentIds, both <code>NULL</code> and not <code>NULL</code>.<br>
  So to sum it up I need to have this format of output for <code>present=1</code>  </p>
  
  <pre><code>    [parentId]  [nbr]
      NULL        0
      10          0
      11          1
  </code></pre>
  
  <p>and this one for <code>present=0</code>    </p>
  
  <pre><code>    [parentId]  [nbr]
      NULL        2
      10          2
      11          0
  </code></pre>
  
  <p>Any help?</p>