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

时间:2021-12-08 19:20:07

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


4  

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


1  

@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


1  

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


4  

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


1  

@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


1  

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';