在一个查询中计数空值和非空值

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 and not null values in column a

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

22 个解决方案

#1

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;

#2

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;

#3

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

#4

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;

#5

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

#6

for non nulls

为非null

select count(a)
from us

for nulls

null值

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

应该做这项工作

#7

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

Query Results

查询结果

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行。其中两行为空。当我们使用Count(*)查询引擎计算行数时，我们得到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(列)时，请确保处理空值，如下所示。

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

will count both NULL and Non-NULL values.

将计算空值和非空值。

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)将给出行总数，而不考虑空值/非空值。只有在使用Count(列)计数列值时，我们才需要处理空值。

#8

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

#9

If you're using MS Sql Server...

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

I don't recomend you doing this... but here you have it (in the same table as result)

我不鼓励你这么做……但这里有(结果在同一个表格中)

#10

use ISNULL embedded function.

使用ISNULL嵌入函数。

---

#11

Here are two solutions:

这里有两个解决方案:

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

OR

或

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

#12

Try

试一试

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

Simple!

简单!

#13

if its mysql, you can try something like this.

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

#14

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

It's fugly, but it will return a single record with 2 cols indicating the count of nulls vs non nulls.

它是fugly，但它将返回一条记录，其中包含两个cols，表示null的计数和非null的计数。

#15

This works in T-SQL. If you're just counting the number of something and you want to include the nulls, use COALESCE instead of case.

这在t - sql工作。如果您只是在计算某物的数量，并且希望包含null，那么请使用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')

#16

Building off of Alberto, I added the rollup.

在阿尔贝托的基础上，我加入了集合。

 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;

#17

Just to provide yet another alternative, Postgres 9.4+ allows applying a FILTER to aggregates:

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

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

#18

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

#19

Just in case you wanted it in a single record:

以防你想把它放在一张唱片里:

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

;-)

:-)

#20

for counting not null values

用于计数非空值

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

for counting null values

计算空值

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

#21

select count(isnull(NullableColumn,-1))

#22

Number of elements where a is null:

a为null的元素个数:

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

Number of elements where a is not null:

a不为空的元素个数:

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