SQL 在单个查询中计算空值和非空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1271810/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Counting null and non-null values in a single query
提问by
I have a table
我有一张桌子
create table us
(
a number
);
Now I have data like:
现在我有这样的数据：
a
1
2
3
4
null
null
null
8
9
Now I need a single query to count null andnot null values in column a
现在我需要一个查询来计算列 a 中的空值和非空值
回答by
This works for Oracle and SQL Server (you might be able to get it to work on another RDBMS):
这适用于 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;
Or:
或者：
select count(*) - count(a), count(a) from us;
回答by Alberto Zaccagni
If I understood correctly you want to count all NULL and all NOT NULL in a column...
如果我理解正确，您想计算列中的所有 NULL 和所有 NOT NULL ......
If that is correct:
如果这是正确的：
SELECT count(*) FROM us WHERE a IS NULL
UNION ALL
SELECT count(*) FROM us WHERE a IS NOT NULL
Edited to have the full query, after reading the comments :]
阅读评论后，已编辑为完整查询：]
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;
回答by christopheml
Here is a quick and dirty version that works on Oracle :
这是一个适用于 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
回答by Ariful Haque
As i understood your query, You just run this script and get Total Null,Total NotNull rows,
据我了解您的查询，您只需运行此脚本并获得 Total Null、Total NotNull 行，
select count(*) - count(a) as 'Null', count(a) as 'Not Null' from us;
回答by EvilTeach
for non nulls
对于非空值
select count(a)
from us
for nulls
对于空值
select count(*)
from us
minus
select count(a)
from us
Hence
因此
SELECT COUNT(A) NOT_NULLS
FROM US
UNION
SELECT COUNT(*) - COUNT(A) NULLS
FROM US
ought to do the job
应该做这份工作
Better in that the column titles come out correct.
更好的是列标题显示正确。
SELECT COUNT(A) NOT_NULL, COUNT(*) - COUNT(A) NULLS
FROM US
In some testing on my system, it costs a full table scan.
在我的系统上的一些测试中，它需要进行一次全表扫描。
回答by elle0087
usually i use this trick
通常我使用这个技巧
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
回答by Abe Voelker
Just to provide yet another alternative, Postgres 9.4+ allows applying a FILTERto aggregates:
只是为了提供另一种选择，Postgres 9.4+允许将 aFILTER应用于聚合：
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
SQLFiddle：http://sqlfiddle.com/#!17/80a24/5
回答by Santhoshkumar LM
This is little tricky. Assume the table has just one column, then the Count(1) and Count(*) will give different values.
这有点棘手。假设表只有一列，那么 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
As you can see in the image, The first result shows the table has 16 rows. out of which two rows are NULL. So when we use Count(*)the query engine counts the number of rows, So we got count result as 16. But in case of Count(empid)it counted the non-NULL-values in the column empid. So we got the result as 14.
正如您在图像中看到的，第一个结果显示该表有 16 行。其中两行是NULL。所以当我们使用Count(*) 时，查询引擎计算行数，所以我们得到的结果为 16。但是在Count(empid) 的情况下，它计算列empid 中的非空值。所以我们得到的结果是 14。
so whenever we are using COUNT(Column) make sure we take care of NULL values as shown below.
所以每当我们使用 COUNT(Column) 时，请确保我们处理 NULL 值，如下所示。
select COUNT(isnull(empid,1)) from @table1
will count both NULL and Non-NULL values.
将计算 NULL 和非 NULL 值。
Note: Same thing applies even when the table is made up of more than one column. Count(1) will give total number of rows irrespective of NULL/Non-NULL values. Only when the column values are counted using Count(Column) we need to take care of NULL values.
注意：即使表格由多列组成，同样的事情也适用。Count(1) 将给出总行数，而不考虑 NULL/非 NULL 值。仅当使用 Count(Column) 计算列值时，我们才需要处理 NULL 值。
回答by Starnuto di topo
I had a similar issue: to count all distinct values, counting null values as 1, too. A simple count doesn't work in this case, as it does not take null values into account.
我有一个类似的问题：计算所有不同的值，也将空值计算为 1。在这种情况下，简单的计数不起作用，因为它不考虑空值。
Here's a snippet that works on SQL and does not involve selection of new values. Basically, once performed the distinct, also return the row number in a new column (n) using the row_number() function, then perform a count on that column:
这是一个适用于 SQL 的片段，不涉及新值的选择。基本上，一旦执行了不同的，还使用 row_number() 函数返回新列 (n) 中的行号，然后对该列执行计数：
SELECT COUNT(n)
FROM (
SELECT *, row_number() OVER (ORDER BY [MyColumn] ASC) n
FROM (
SELECT DISTINCT [MyColumn]
FROM [MyTable]
) items
) distinctItems
回答by Rodrigo Prazim
Try
尝试
SELECT
SUM(ISNULL(a)) AS all_null,
SUM(!ISNULL(a)) AS all_not_null
FROM us;
Simple!
简单的！
