首页 文章

在单个查询中计算null和非null值

提问于
浏览 次
99

我有一张 table

create table us
(
 a number
);

现在我有以下数据：

a
1
2
3
4
null
null
null
8
9

现在我需要一个查询来计算列a中的null and not null值

sql

24 回答

  • -2

    使用ISNULL嵌入式功能 .

    回复于
  • 15

    如果它是mysql，你可以试试这样的东西 .

    select 
   (select count(*) from TABLENAME WHERE a = 'null') as total_null, 
   (select count(*) from TABLENAME WHERE a != 'null') as total_not_null
FROM TABLENAME
    回复于
  • 0

    这有两个解决方案：

    Select count(columnname) as countofNotNulls, count(isnull(columnname,1))-count(columnname) AS Countofnulls from table name

    要么

    Select count(columnname) as countofNotNulls, count(*)-count(columnname) AS Countofnulls from table name
    回复于
  • 0

    尝试

    SELECT 
   SUM(ISNULL(a)) AS all_null,
   SUM(!ISNULL(a)) AS all_not_null
FROM us;

    简单！

    回复于
  • 48

    我有一个类似的问题：计算所有不同的值，将空值计算为1 . 在这种情况下，简单计数不起作用，因为它不考虑空值 .

    这是一个适用于SQL的代码片段，不涉及选择新值 . 基本上，一旦执行了distinct，也使用row_number（）函数返回新列（n）中的行号，然后对该列执行计数：

    SELECT COUNT(n)
FROM (
    SELECT *, row_number() OVER (ORDER BY [MyColumn] ASC) n
    FROM (
        SELECT DISTINCT [MyColumn]
                    FROM [MyTable]
        ) items  
) distinctItems
    回复于
  • 180

    只是为了提供另一种选择，Postgres 9.4 allows applying a FILTER to aggregates：

    SELECT
  COUNT(*) FILTER (WHERE a IS NULL) count_nulls,
  COUNT(*) FILTER (WHERE a IS NOT NULL) count_not_nulls
FROM us;

    SQLFiddle：http://sqlfiddle.com/#!17/80a24/5

    回复于
  • 1

    这适用于Oracle和SQL Server（您可以将其用于另一个RDBMS）：

    select sum(case when a is null then 1 else 0 end) count_nulls
     , count(a) count_not_nulls 
  from us;

    要么：

    select count(*) - count(a), count(a) from us;
    回复于
  • -1

    如果我理解正确，你想要计算列中的所有NULL和所有NOT NULL ...

    如果这是正确的：

    SELECT count(*) FROM us WHERE a IS NULL 
UNION ALL
SELECT count(*) FROM us WHERE a IS NOT NULL

    阅读评论后编辑完整查询：]

    SELECT COUNT(*), 'null_tally' AS narrative 
  FROM us 
 WHERE a IS NULL 
UNION
SELECT COUNT(*), 'not_null_tally' AS narrative 
  FROM us 
 WHERE a IS NOT NULL;
    回复于
  • 1

    这是一个适用于Oracle的快速和脏版本：

    select sum(case a when null then 1 else 0) "Null values",
       sum(case a when null then 0 else 1) "Non-null values"
from us
    回复于
  • 0

    正如我理解您的查询，您只需运行此脚本并获取Total Null，Total NotNull行，

    select count(*) - count(a) as 'Null', count(a) as 'Not Null' from us;
    回复于
  • 2

    通常我会用这个技巧

    select sum(case when a is null then 0 else 1 end) as count_notnull,
       sum(case when a is null then 1 else 0 end) as count_null
from tab
group by a
    回复于
  • 36

    对于非空值

    select count(a)
from us

    对于空值

    select count(*)
from us

minus 

select count(a)
from us

    于是

    SELECT COUNT(A) NOT_NULLS
FROM US

UNION

SELECT COUNT(*) - COUNT(A) NULLS
FROM US

    应该做的工作

    回复于
  • 0

    这有点棘手 . 假设表只有一列，则Count（1）和Count（*）将给出不同的值 .

    set nocount on
    declare @table1 table (empid int)
    insert @table1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(NULL),(11),(12),(NULL),(13),(14);

    select * from @table1
    select COUNT(1) as "COUNT(1)" from @table1
    select COUNT(empid) "Count(empid)" from @table1

    Query Results

    正如您在图像中看到的，第一个结果显示该表有16行 . 其中两行为NULL . 因此，当我们使用 Count(*) 时，查询引擎计算行数，因此我们得到计数结果为16.但是在 Count(empid) 的情况下，它计算列empid中的非NULL值 . 所以我们得到的结果为14 .

    因此，每当我们使用COUNT（Column）时，请确保我们处理NULL值，如下所示 .

    select COUNT(isnull(empid,1)) from @table1

    将计算NULL和非NULL值 .

    Note ：即使表格由多个列组成，也同样适用 . Count（1）将给出总行数，而不管NULL /非NULL值 . 只有当使用Count（Column）计算列值时，我们才需要处理NULL值 .

    回复于
  • 2

    如果您正在使用MS Sql Server ...

    SELECT COUNT(0) AS 'Null_ColumnA_Records',
(
    SELECT COUNT(0)
    FROM your_table
    WHERE ColumnA IS NOT NULL
) AS 'NOT_Null_ColumnA_Records'
FROM your_table
WHERE ColumnA IS NULL;

    我不建议你这样做......但是你在这里（结果在同一张表中）

    回复于
  • 11
    SELECT SUM(NULLs) AS 'NULLS', SUM(NOTNULLs) AS 'NOTNULLs' FROM 
    (select count(*) AS 'NULLs', 0 as 'NOTNULLs' FROM us WHERE a is null
    UNION select 0 as 'NULLs', count(*) AS 'NOTNULLs' FROM us WHERE a is not null) AS x

    它很难看，但它将返回一个带有2个cols的记录，表示空值与非空值的计数 .

    回复于
  • -1

    这适用于T-SQL . 如果您只计算某些内容并且想要包含空值，请使用COALESCE而不是大小写 .

    IF OBJECT_ID('tempdb..#us') IS NOT NULL
    DROP TABLE #us

CREATE TABLE #us
    (
    a INT NULL
    );

INSERT INTO #us VALUES (1),(2),(3),(4),(NULL),(NULL),(NULL),(8),(9)

SELECT * FROM #us

SELECT CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END AS 'NULL?',
        COUNT(CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END) AS 'Count'
    FROM #us
    GROUP BY CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END

SELECT COALESCE(CAST(a AS NVARCHAR),'NULL') AS a,
        COUNT(COALESCE(CAST(a AS NVARCHAR),'NULL')) AS 'Count'
    FROM #us
    GROUP BY COALESCE(CAST(a AS NVARCHAR),'NULL')
    回复于
  • 1

    Build 在Alberto的基础上，我添加了汇总 .

    SELECT [Narrative] = CASE 
 WHEN [Narrative] IS NULL THEN 'count_total' ELSE    [Narrative] END
,[Count]=SUM([Count]) FROM (SELECT COUNT(*) [Count], 'count_nulls' AS [Narrative]  
FROM [CrmDW].[CRM].[User]  
WHERE [EmployeeID] IS NULL 
UNION
SELECT COUNT(*), 'count_not_nulls ' AS narrative 
FROM [CrmDW].[CRM].[User] 
WHERE [EmployeeID] IS NOT NULL) S 
GROUP BY [Narrative] WITH CUBE;
    回复于
  • 0
    SELECT
    ALL_VALUES
    ,COUNT(ALL_VALUES)
FROM(
        SELECT 
        NVL2(A,'NOT NULL','NULL') AS ALL_VALUES 
        ,NVL(A,0)
        FROM US
)
GROUP BY ALL_VALUES
    回复于
  • 4
    select count(isnull(NullableColumn,-1))
    回复于
  • 0

    所有答案都是错误的或非常过时的 .

    执行此查询的简单而正确的方法是使用 COUNT_IF 函数 .

    SELECT
  COUNT_IF(a IS NULL) AS nulls,
  COUNT_IF(a IS NOT NULL) AS not_nulls
FROM
  us
    回复于
  • 5

    试试这个..

    SELECT CASE 
         WHEN a IS NULL THEN 'Null' 
         ELSE 'Not Null' 
       END a, 
       Count(1) 
FROM   us 
GROUP  BY CASE 
            WHEN a IS NULL THEN 'Null' 
            ELSE 'Not Null' 
          END
    回复于
  • 0

    万一你想在一个记录中：

    select 
  (select count(*) from tbl where colName is null) Nulls,
  (select count(*) from tbl where colName is not null) NonNulls

    ;-)

    回复于
  • 1

    用于计算非空值

    select count(*) from us where a is not null;

    用于计算空值

    select count(*) from us where a is null;
    回复于
  • 0

    a为null的元素数：

    select count(a) from us where a is null;

    a不为null的元素数：

    select count(a) from us where a is not null;
    回复于

相关问题