Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Counting null and non-null values in a single query

Tags:

sql

People also ask

Does count (*) ignore NULL values?

COUNT does not include NULL values in column counts. Therefore, the number of return values for each column might differ or be less than the total number of rows returned by COUNT(*).

How do I count non-NULL rows in SQL?

Using SELECT COUNT(*) or SELECT COUNT(1) (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values. Using COUNT()will count the number of non-NULL items in the specified column (NULL fields will be ignored).

How do I count with Isnull in SQL?

If you want the COUNT function to count all rows of a given column, including the null values, use the ISNULL function. The ISNULL function can replace the null value with a valid value. or other SET operation. (1) - NULL values are eliminated.


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;

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;

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

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.


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;

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

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