CODEWITHSUNDEEP
sqloracle-databaseplsqlcount
die_zauberin
die_zauberin

Reputation: 1

count number of null and not null rows per attribute in oracle

Is there is a solution how to calculate the number of NULL and NOT NULL records per each attribute in the view?

For example there are 50 views and each one has 20 attributes and the result I'm expecting looks like (for example):

table_name -----Column_name---Nulls_count----Not_null_count------count(*)

T1 -----------------C1-------------------20---------------40-----------------------60          
T1------------------C2-------------------11--------------49---------------------60
T1------------------C3-------------------25--------------35---------------------60
T2------------------C1-------------------0--------------100---------------------100
T2------------------C2-------------------40--------------60---------------------100

all of views are stored in a sys.all_views and columns are in the sys.all_tab_columns and there is a link between them by table_name field. But there is a need to use a dynamic SQL or PL/SQL becouse there is a madness to count() null rows for each attribute and then to count() not null rows for the same attributes in the views manually :) Did anyone face with such task? I'll appreciate all your comments and help.

Upvotes: 0

Views: 1601

Answers (2)

die_zauberin
die_zauberin

Reputation: 1

here is the correct select:

 select t.table_name, T.NUM_ROWS, c.column_name, c.num_nulls, T.NUM_ROWS -  c.num_nulls  num_not_nulls, c.data_type, c.last_analyzed
from all_tab_cols c
join sys.all_all_tables t on C.TABLE_NAME = t.table_name
 where c.table_name like 'MV_%' and c.nullable ='Y'
 group by t.table_name, T.NUM_ROWS, c.column_name, c.num_nulls, c.data_type, c.last_analyzed
  order by t.table_name, c.column_name;

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52356

Since the number of rows is count(*), you can get null and non-null rows per column with:

select
  count(*)               total_rows  ,
  count(col1)            col1_nonnull,
  count(*) - count(col1) col1_null   ,
  count(col2)            col2_nonnull,
  count(*) - count(col2) col2_null   ,
  ...
from
  my_view

Upvotes: 1

Related Questions