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?
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 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.
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(*).
The COUNT function counts the number of records that have non-NULL values in a field for each GROUP BY result.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With