10

How can I count only NULL values in Oracle/PLSQL?

I want to count only the null values. Is there a function that does that?

APC
  • 144,005
  • 19
  • 170
  • 281
Dan F.
  • 345
  • 1
  • 3
  • 12

8 Answers8

19

I don't know Oracle specifally, but ANSI SQL, COUNT(rowName) does not count NULL values, but COUNT(*) does. So you can write

SELECT COUNT(*) FROM YourTable WHERE YourColumn IS NULL

which counts the rows in YourTable that have YourColumn set to NULL.

mdma
  • 56,943
  • 12
  • 94
  • 128
  • In my Oracle database it does not work. A count results in 0, because null can not be counted. We use a coalesce to solve that. – cybork Feb 18 '16 at 19:27
11

As an alternative to mdma's response. If you don't want to put a filter in the where you can

SELECT COUNT(case when xxx IS NULL THEN 1 end) cnt_xxx_null
FROM table
Gary Myers
  • 34,963
  • 3
  • 49
  • 74
7

The Oracle documentation states that:

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null.

As an example, using the scott schema: 

SQL> select empno, sal, comm
  2  from emp;

     EMPNO        SAL       COMM
---------- ---------- ----------
      7369        800
      7499       1600        300
      7521       1250        500
      7566       2975
      7654       1250       1400
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500          0
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

You can see that the Comm column has 4 known values (i.e. Not null) and 10 unknown values (i.e. Null)

As count(your_column_name) ignores nulls you need to substitute the unknown values for something you can refer to. This can be achieved using the NVL function.

SQL> select count(nvl(comm, -1)) "number of null values"
  2  from emp
  3  where nvl(comm, -1) = -1;

number of null values
---------------------
                   10

I have used the value "-1" as the "alias" for my null values because I know that "-1" is not an existing value within the comm column.

EDIT:

Following Rob's suggestion. It is possible to remove the where clause from the above example and use the NVL2 function as shown below:

SQL> select count(nvl2(comm,null,-1)) "number of null values"
  2  from emp
  3  /

number of null values
---------------------
                   10
0xdb
  • 3,539
  • 1
  • 21
  • 37
Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
1

If you wants to count other values too with null then use of COALESCE function will improves execution time

Oracle Differences between NVL and Coalesce

SELECT COUNT(COALESCE( _COLUMN, 1)) AS CNT FROM _TABLE
Community
  • 1
  • 1
Imran
  • 2,906
  • 1
  • 19
  • 20
  • And if the column you're counting, replace the '1' by ''1'' or 'ábc' or somthing. By the way, we also change it with coalesce to something that can't happen in the reality of the database. Anyway. So for a table that has chars: SELECT COUNT(COALESCE( _COLUMN, 'abc')) AS CNT FROM _TABLE – cybork Feb 18 '16 at 19:30
1

I might try to inverse the null, see results

SELECT
 COUNT(DECODE(YourField, null, 1, null)) Nulls,
 count(*) Everything,
 COUNT(YourField) NotNulls
FROM YourTable

Everything should equal nulls + notnulls

slavoo
  • 5,798
  • 64
  • 37
  • 39
armagedescu
  • 1,758
  • 2
  • 20
  • 31
0
select count(nvl(values, 0)) from emp where values is null;
mikcutu
  • 1,013
  • 2
  • 17
  • 34
0

Function:

create or replace function xxhrs_fb_count_null
return number
as
l_count_null number;
begin
  select count(*) into l_count_null from emp where comm is null;
  return l_count_null;
end;

Usage:

select xxhrs_fb_count_null from dual
armagedescu
  • 1,758
  • 2
  • 20
  • 31
vivek
  • 1
0

I believe your requirement is as below: Table emp has 100 rows. Against 20 employees, HIRE_DATE column is NULL. So basically, you want to get 20 as output. This is another method apart from the answers given by other contributors in this forum.

-- COUNT (1) would return 100
-- COUNT (hire_date) would return 80
-- 100 - 80 = 20
SELECT   COUNT (1) -
                 COUNT (hire_date)
                 AS null_count
FROM      emp;
Shinto Joy
  • 31
  • 6