Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

oracle

plsql

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?

like image 918
Dan F. Avatar asked May 15 '10 19:05

Dan F.


People also ask

How do I COUNT only NULL values in SQL?

Here's the simplest way to count NULL values in SQL The easiest way to count the NULLs in a column is to combine COUNT(*) with WHERE <column name> IS NULL . This is a common and fundamental data quality check.

How do you COUNT NULL values?

How to Count SQL NULL values in a column? The COUNT() function is used to obtain the total number of the rows in the result set. When we use this function with the star sign it count all rows from the table regardless of NULL values.

Does COUNT () include NULL?

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(*).

Does COUNT include NULL in Oracle?

The COUNT function counts the number of records that have non-NULL values in a field for each GROUP BY result.


2 Answers

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
like image 86
Gary Myers Avatar answered Sep 22 '22 14:09

Gary Myers


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
like image 32
Ian Carpenter Avatar answered Sep 22 '22 14:09

Ian Carpenter