Oracle count null and not null values in column

In this article we will post two ways of counting null and not null values per table(s) by union or single query with Oracle 11g.

Counting null / not null single table single query with LISTAGG

You can check all tables in Oracle schema for null and/or not null values by single query. In creating this query we are going to list LISTAGG function which is extremely helpful for such situations. It's used for pivot/unpivot data. The example use this schema and table:

• schema: myowner
• table: mytable

Step 1 Prepare select collecting values per table

SELECT 'select '
  || LISTAGG( column_name, ' ' ) WITHIN GROUP (
ORDER BY table_name)
  || '''mytable'' from myowner.mytable' "names"
FROM
  (SELECT *
  FROM
    (SELECT 'count('
      || column_name
      || '), '         AS column_name,
      'MBCPF_COVERAGE' AS table_name
    FROM all_tab_cols
    WHERE table_name = 'mytable'
    AND owner        = 'myowner'
    )
  )
GROUP BY table_name;

result is the select of step 2

Step 2 Counting null and not null values

SELECT COUNT(ID),
  COUNT(STATUS),
  COUNT(ODATE),
  COUNT(SDATE),
  'mytable'
FROM myowner.mytable

result:

Counting null / not null by union in Oracle

We can do similar operation with a small difference - the output will be in rows and not columns like previous one:

• schema: myowner
• table: mytable

Step 1 Prepare select collecting values per table

SELECT 'select count('
  || column_name
  || '),     '''
  || column_name
  || ''' from myowner.mytable union' AS table_name
FROM all_tab_cols
WHERE table_name = 'mytable'
AND owner        = 'myowner';

result is the select of step 2

Step 2 Counting null and not null values

select count(ID),     'ID' from myowner.mytable union
select count(STATUS),     'STATUS' from myowner.mytable union
select count(ODATE),     'ODATE' from myowner.mytable union
select count(SDATE),     'SDATE' from myowner.mytable;

result:

Which way you are going to use depends on your needs and preferences. You may check also how to do the same operation in MySQL:

MySQL 5.7 count null and not null values in column