239

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

Improve this question
3
  • 3
    Hi, where do you need to this kind of counting database code in what language database are we talking Best Regards, Iordan Commented Aug 13, 2009 at 13:03
  • 2
    ??? Isn't it just this ??? SELECT count(*) FROM us WHERE a IS NULL or a IS NOT NULL works in Postgres Commented Jan 16, 2019 at 4:38
  • 1
    simply do select count(*) - count(a) as num_null, count(a) as num_not_null from us; counting a column returns the number of non null values, substract it from count(*) and you're gold. Commented Mar 1, 2024 at 10:26

27 Answers 27

Reset to default
343

This works for Oracle and SQL Server (you might be able to get it to work on another 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;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Using the distinction between count(*) and count(a) also works well with group by
@shannon I agree, COUNT(a) is a useful comment to add, but this does throw a warning/error depending on your stack and might warrant a comment in code. I would prefer the SUM method.
Prefer count(*) to count(1)
@LeiZhao - Does it make any difference in speed?
@Ωmega No difference in speed, I believe, at least for any recent version of RDBMS (within one or two decades). I preferred count(*) not for performance, but for clarity of meaning.
|
88

If I understood correctly you want to count all NULL and all NOT NULL in a column...

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;
Improve this answer

Comments

56

Here is a quick and dirty version that works on 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
Improve this answer

10 Comments

Similar syntax would work in SQL Server as well. Also, doing it this way will only scan the table once; the UNION solutions will do two table scans. Irrelevant for small tables, very important for huge ones.
Only change for SQL Server is "Null values" would have to become 'Null values'. Single quotes, not doubles.
SQLServer uses an index scan for this query vs two index seeks using a union. On a table with 40.000 rows, there is no speed difference.
On a table with 11.332.581 rows, there are two table scans, no noticable speed difference (actualy, the union is slightly faster).
This did not work for me in Oracle 11g. The version @user155789 posted with "case when a is null then 1 else 0 end" was the syntax that worked.
|
36

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.

Improve this answer

3 Comments

Good gravy, man, look at the execution plans for these queries. You're kicking off table scans left and right, especially where there's such a bloody simple statement (select count(*) from t where a is null) that does this.
I don't have a database handy to look, but either the column is indexed or not. If it is, it happens via a range scan, otherwise, you are pretty much left with a full table scan. In oracle, NULLs are not stored in the index, so I suspect you example isn't much better. Your milage may very.
@EvilTeach: Indexes are only helpful when you're not pulling back > ~10% of the rows. After that, full scans are initiated. In this case, you'll get the scan at least once, if not twice.
29

Just to provide yet another alternative, 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

Improve this answer

Comments

28

As i understood your query, You just run this script and get Total Null,Total NotNull rows,

select count(*) - count(a) as 'Null', count(a) as 'Not Null' from us;
Improve this answer

Comments

27

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
Improve this answer

1 Comment

You need to swap count_notnull and count_null, now it gives opposite results :)
6

This is little tricky. Assume the table has just one column, then the Count(1) and Count(*) will give different values.

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.

so whenever we are using COUNT(Column) make sure we take care of NULL values as shown below.

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.

Improve this answer

Comments

4

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.

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:

SELECT COUNT(n)
FROM (
    SELECT *, row_number() OVER (ORDER BY [MyColumn] ASC) n
    FROM (
        SELECT DISTINCT [MyColumn]
                    FROM [MyTable]
        ) items  
) distinctItems
Improve this answer

Comments

4

Try this..

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
Improve this answer

Comments

3

use ISNULL embedded function.

Improve this answer

1 Comment

This is also a worthy answer. I personally found that COUNT(DISTINCT ISNULL(A,'')) works even better then COUNT(DISTINCT A) + SUM (CASE WHEN A IS NULL THEN 1 ELSE 0 END)
3

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
Improve this answer

Comments

3

Try

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

Simple!

Improve this answer

Comments

3 
select count(isnull(NullableColumn,-1))
Improve this answer

1 Comment

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.
2

If you're using 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)

Improve this answer

Comments

2

All the answers are either wrong or extremely out of date.

The simple and correct way of doing this query is using COUNT_IF function.

SELECT
  COUNT_IF(a IS NULL) AS nulls,
  COUNT_IF(a IS NOT NULL) AS not_nulls
FROM
  us
Improve this answer

Comments

1 
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.

Improve this answer

Comments

1

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.

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')
Improve this answer

Comments

1

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;
Improve this answer

Comments

1 
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
Improve this answer

Comments

0

if its mysql, you can try something like this.

select 
   (select count(*) from TABLENAME WHERE a = 'null') as total_null, 
   (select count(*) from TABLENAME WHERE a != 'null') as total_not_null
FROM TABLENAME
Improve this answer

Comments

0

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

;-)

Improve this answer

Comments

0

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;
Improve this answer

1 Comment

The op asked for single query :)
0

I created the table in postgres 10 and both of the following worked:

select count(*) from us

and

select count(a is null) from us

Improve this answer

2 Comments

a IS NULL produces TRUE or FALSE, and COUNT() will count all NOT NULL values. So count(a is null) will return the count of all rows.
@ypresto I agree with you, the answer is incorrect
0

In my case I wanted the "null distribution" amongst multiple columns:

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,...

As per the '...' it is easily extendable to more columns, as many as needed

Improve this answer

Comments

0

I wanted the count of all values (including NULL) in MySQL and this worked:

SELECT
  _a,
  COUNT(_a)
FROM
  (
    SELECT
      COALESCE(a, 'NULL') as _a
    FROM
      us
  ) AS T
GROUP BY
  _a;
Improve this answer

Comments

-4

Number of elements where a is null:

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

Number of elements where a is not null:

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

1 Comment

The question is for a single query.