counting null values in sql with where and group by clause

Question

Here is the sample data for test table I have

[childId] [parentId] [present]
1         10         0
2         11         1
3         NULL       0
4         NULL       0
NULL      10         0

Now, for all the parentIds (including those with NULL) I want to count all the present childIds

select parentId, count(childId) as nbr
from TestTable
where present=1 or parentId is NULL
group by parentId

The result I get is

parentId    nbr
NULL        2
11          1

Same count number (nbr) I get for both present=1 and present=0. It seems that I cannot impose a condition for the NULL grouped value.
What's the mistake I'm making and what's the solution for the query I want to make?

---

UPDATE:

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.

I created a view with full outer join, so I have some records having NULL for childId and some records having NULL for parentId.
Now, I want to list all of the parentId values (both NULL and non NULL) and for each of them, count all the records that, for example, have present=1. If I use where present=1 condition, it eliminates NULL values from result set, i.e. I won't have result record NULL|x for parentId|nbr.
I solved this with union all. In first select I have where present=1 and in second select I have where present=1 and parentId is NULL.

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

The result I get is

    [parentId]  [nbr]
    NULL        2
    10          1
    NULL        2

The only problem with this (besides duplicated record for parentId=NULL), is that if there are no records with parentId=NULL and present=1, in result I won't have record with NULL | 0 for parentId|nbr and I want to list ALL parentIds, both NULL and not NULL.
So to sum it up I need to have this format of output for present=1

    [parentId]  [nbr]
    NULL        0
    10          0
    11          1

and this one for present=0

    [parentId]  [nbr]
    NULL        2
    10          2
    11          0

Any help?

Solution 1

where present=1 or parentId is NULL

That would be it. Either you have Present = 1 or parentID is null. There's no clause saying that parentID should be null and present should be 1.

What exactly are you trying to accomplish?

To list all non-null parentID that has present = 1 and all null parentID regardless of present, you can use your where clause, but edit your group by to GROUP BY parentid, present. This way you will show all non-null with present 1, and all null, both present 0 and 1.

UPDATE: Based on your new requirements, you need to also group for non-existing combinations. Nowhere in your dataset is there a NULL, 1 value - so your ordinary way of looking at it won't make much sense. You need to separate the parentid and present from eachother.

One way to do this is to simply do the following:

SELECT parentID, Pres1.nbr_pres_1, Pres0.nbr_pres_0
FROM t
OUTER APPLY (SELECT COUNT(1) as nbr_pres_1 FROM t t1 WHERE 
             coalesce( t.parentid , -999) = coalesce(t1.parentid ,-999) and present=1 ) Pres1
OUTER APPLY (SELECT COUNT(1) as nbr_pres_0 FROM t t0 WHERE 
             coalesce( t.parentid , -999) = coalesce(t0.parentid ,-999) and present=0 ) Pres0
GROUP BY t.ParentID, Pres1.nbr_pres_1, Pres0.nbr_pres_0

This is based on testing using sqlfiddle and your sample data set. http://sqlfiddle.com/#!3/8d7f6/29

Solution 2

Use a SUM aggregation of the present field, casting the bit field to integer, as follows:

SELECT parentId, SUM(CAST(present AS INTEGER)) as nbr
FROM TestTable
GROUP BY parentId

Solution 3

If this is the result you are looking for:

pId    nbr
NULL   1
10     0
11     1

Then I think this is the sql you are looking for(not tested yet, no server available):

select distinct 
  parentId as pId, 
  (select count(*) from TestTable where parentId=pId and present=1) as nbr
from TestTable 
group by parentId