需要帮助计算SQL中同一表中的不同值。

I'm new to SQL and I'm having trouble with a count query. I want to count the number of results that return a value and also return a second count if the value is null.

我是SQL新手，我在count查询上遇到了麻烦。我希望计算返回值的结果的数量，如果值为空，还希望返回第二个计数。

Here is what I have so far. If anyone can help I would appreciate it. Thanks.

这是我到目前为止所得到的。如果有人能帮忙，我将不胜感激。谢谢。

Select
    Sum(Case When Column name = '!NULL' Then 1 Else 0 End) as [Policy ID],
    Sum(Case When Column name = 'NULL' Then 1 Else 0 End) as [No Policy Id]
    --Count(*) as [Total]
From 
    table.name
Where 
    columnname >= '2016-01-01'

3 个解决方案

#1

Use IS NULL and IS NOT NULL instead of checking nulls with equality:

使用为NULL，并不是NULL，而是以相等的方式检查NULL:

SELECT
    SUM(CASE WHEN Column_name IS NOT NULL THEN 1 ELSE 0 END) AS [Policy ID],
    SUM(CASE WHEN Column_name IS NULL     THEN 1 ELSE 0 END) AS [No Policy Id]
--COUNT(*) AS [Total]
FROM table.name
WHERE columnname >= '2016-01-01'

In SQL the value NULL means "unknown" and hence comparing a column value against it using = also yields an unknown result. Instead, use IS NULL or IS NOT NULL.

在SQL中，值NULL表示“未知”，因此使用=对列值进行比较也会产生未知结果。相反，使用是NULL或者不是NULL。

#2

@TimBiegelesien's answer is right the only thing I would add/suggest would be if ColumnName contains an Empty string ('') and you want to count it as NULL you could do something like this:

@TimBiegelesien的答案是对的我唯一要添加/建议的是如果ColumnName包含一个空字符串(")你想把它算作空你可以这样做:

Select
Sum(Case When LENGTH(ColumnName) > 0 Then 1 Else 0 End) as [Policy ID],
Sum(Case When LENGTH(ColumnName) < 1 Then 1 Else 0 End) as [No Policy Id]
--Count(*) as [Total]
From table.name
Where columnname >= '2016-01-01'

Note in some rdbms LENGTH is actually LEN

注意，某些rdbms长度实际上是LEN

Select
Sum(Case When LEN(ColumnName) > 0 Then 1 Else 0 End) as [Policy ID],
Sum(Case When LEN(ColumnName) < 1 Then 1 Else 0 End) as [No Policy Id]
--Count(*) as [Total]
From table.name
Where columnname >= '2016-01-01'

This will still work even if the datatype is numeric (int, bigint, etc.)

即使数据类型是数值的(int, bigint，等等)，这仍然有效。

#3

Here is an example using two separate queries:

下面是一个使用两个独立查询的示例:

    SELECT COUNT(CASE WHEN LEN(columnname) > 1 THEN 1 ELSE 0 END) AS [Policy ID] FROM table.name
WHERE columnname >= '2016-01-01';

    SELECT COUNT(CASE WHEN columnname IS NULL THEN 1 ELSE NULL END)
 AS [No Policy ID] FROM table.name
WHERE columnname >= '2016-01-01';

#1