使用where和group by子句计算sql中的空值

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

现在,对于所有parentIds(包括那些具有NULL的),我想计算所有当前的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?

相同的计数(nbr)我得到的是present = 1和present = 0。似乎我不能为NULL分组值强加条件。我正在犯的错误是什么,我想要查询的解决方案是什么?

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.

我创建了一个带有完全外连接的视图,所以我有一些记录对于childId有NULL,有些记录对于parentId有NULL。现在,我想列出所有parentId值(NULL和非NULL),并为每个值计算所有记录,例如,存在= 1。如果我使用present = 1条件,它会从结果集中删除NULL值,即对于parentId | nbr,我不会有结果记录NULL | x。我用联盟解决了这个问题。在第一个选择中,我有where = 1,在第二个选择中我有present = 1,parentId为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 = NULL的重复记录),如果没有parentId = NULL且present = 1的记录,结果我将没有记录为NULL | 0表示parentId | nbr,我想列出所有parentIds,NULL和非NULL。总而言之,我需要为present = 1提供这种格式的输出

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

and this one for present=0

而这一个为现在= 0

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

Any help?

4 个解决方案

#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.

就是这样。您有Present = 1或parentID为null。没有任何条款说parentID应该为null,并且present应该为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.

要列出所有具有present = 1且所有null parentID的非null parentID,无论是否存在,您都可以使用where子句,但要编辑组到GROUP BY parentid,present。这样,您将显示所有非null,当前为1,并且全为null,均为0和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.

更新:根据您的新要求,您还需要对不存在的组合进行分组。你的数据集中没有任何地方有一个NULL,1值 - 所以你看待它的普通方式没有多大意义。你需要将parentid和present分开。

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

这是基于使用sqlfiddle和您的示例数据集进行的测试。 http://sqlfiddle.com/#!3/8d7f6/29

#2

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

使用当前字段的SUM聚合,将位字段转换为整数,如下所示:

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

#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):

然后我认为这是你正在寻找的sql(尚未测试,没有服务器可用):

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

#4

Try below mentioned code snippet.

请尝试以下提到的代码段。

SELECT ParentId, COUNT(ChildId) nbr FROM SO

Where (Present = 1) OR (ParentId IS NULL AND Present = 1) Group by ParentId

Where(Present = 1)OR(ParentId IS NULL AND Present = 1)按ParentId分组

--OR You can try below mentioned code as well.

- 或者您也可以尝试下面提到的代码。

SELECT ParentId, COUNT(ChildId) nbr FROM SO

Where Present = 1 Group by ParentId

Where Present = 1由ParentId分组

#1

where present=1 or parentId is NULL