在单个查询中计算空值和非空值的个数

我有一张表

create table us
(
a number
);

现在我有如下数据:

a
1
2
3
4
null
null
null
8
9

现在我需要一个查询来计数列a中的空而且非空值

554269 次浏览
小开

a为空的元素个数:

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

a不为空的元素个数:

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

对于非空值

select count(a)
from us

null值

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

应该做这项工作

更好的是列标题是正确的。

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

在我的系统上进行的一些测试中，需要进行全表扫描。

小开

如果是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
小开

下面是一个在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
小开

如果我理解正确，你想在一个列中计数所有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;
小开

这适用于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;
小开

如果你正在使用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;

我不建议你这么做……但这里你有它(在同一张表中的结果)

小开 
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的记录，指示null和非null的计数。

小开

使用ISNULL嵌入函数。

小开

以防你想把它记录在一条记录里:

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

: -)

小开

这里有两种解决方案:

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
小开 
select count(isnull(NullableColumn,-1))
小开

我有一个类似的问题:要计算所有不同的值，空值也算作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
小开

试一试

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

简单!

小开

用于计数非空值

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

用于计算空值

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

这在T-SQL中有效。如果你只是计算一些东西的数量，你想包括空值，使用COALESCE而不是case。

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')
小开

在阿尔贝托的基础上，我添加了汇总。

 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;
小开

正如我理解你的查询，你只需要运行这个脚本并获得Total Null,Total NotNull行，

select count(*) - count(a) as 'Null', count(a) as 'Not Null' from us;
小开

这有点棘手。假设表只有一列，那么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

查询结果

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

所以每当我们使用计数(列)，确保我们照顾空值如下所示。

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

将同时计算NULL和Non-NULL值。

请注意:当表由多个列组成时，同样的事情也适用。Count(1)将给出总行数，而不考虑NULL/Non-NULL值。只有在使用Count(column)对列值进行计数时，我们才需要注意NULL值。

小开

我通常用这个技巧

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
小开

为了提供另一种选择，Postgres 9.4+ 允许对聚合应用FILTER:

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

小开 
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
小开

所有的答案要么是错误的，要么是非常过时的。

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

SELECT
COUNT_IF(a IS NULL) AS nulls,
COUNT_IF(a IS NOT NULL) AS not_nulls
FROM
us
小开

试试这个. .

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
小开

我在postgres 10中创建了这个表，下面两种方法都有效:

select count(*) from us

而且

select count(a is null) from us

小开

在我的例子中，我想要“空分布”在多个列中:

SELECT
(CASE WHEN a IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS a_null,
(CASE WHEN b IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS b_null,
(CASE WHEN c IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS c_null,
...
count(*)
FROM us
GROUP BY 1, 2, 3,...
ORDER BY 1, 2, 3,...

根据“……它可以很容易地扩展到更多的列，只要需要


提交答案