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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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