Vertica. Count of Null and Not-Null of all columns of a Table

Question

How can we get null and non-null counts of all columns of a Table in Vertica? Table can have n number of columns and for each column we need to get count of nulls and non-nulls values of that table.

For Example. Below Table has two columns

column1 Column2
1         abc
          pqr
3
          asd
5

If its a specific column then we can check like

SELECT COUNT(*) FROM table where column1 is null;
SELECT COUNT(*) FROM table where column1 is not null;

Same query for column2

I checked system tables like projection_storage and others but I cant figure out a generic query which gives details by hard coding only TABLE NAME in the query.

Answer 1

Hello @user2452689: Here is a dynamically generated VSQL statement which meets your requirement of counting nulls & not nulls in N columns. Notice that this writes a temporary SQL file out to your working directory, and then execute it via the \i command. You only need to change the first two variables per table. Hope this helps - good luck! :-D

--CHANGE SCHEMA AND TABLE PARAMETERS ONLY:
\set table_schema '\'public\''
\set table_name '\'dim_promotion\''
---------
\o temp_sql_file
\pset tuples_only
select e'select \'' || :table_schema || e'\.' || :table_name || e'\' as table_source' as txt
union all
select * from (
select 
', sum(case when ' || column_name || ' is not null then 1 else 0 end) as ' || column_name || '_NOT_NULL
, sum(case when ' || column_name || ' is null then 1 else 0 end) as ' || column_name || '_NULL' as txt
from columns
where table_schema = :table_schema
and table_name = :table_name
order by ordinal_position
) x
union all
select ' from ' || :table_schema || e'.' || :table_name || ';' as txt ;
\o
\pset tuples_only
\i temp_sql_file

Answer 2

You can use:

select count(*) as cnt,
       count(column1) as cnt_column1,
       count(column2) as cnt_column2
from t;

count() with a column name or expression counts the number of non-NULL values in the column/expression.

(Obviously, the number of NULL values is cnt - cnt_columnX.)

Answer 3

select  column1_not_null
       ,column2_not_null
       ,column3_not_null

       ,cnt - column1_not_null as column1_null
       ,cnt - column2_not_null as column2_null
       ,cnt - column3_not_null as column3_null


from   (select  count(*)         as cnt

               ,count (column1)  as column1_not_null
               ,count (column2)  as column2_not_null
               ,count (column3)  as column3_not_null

        from    mytable
        ) t