SQL: Get percentage value of row that completed by values and don't count null values

Mohammad Shohag 0
Viewed 22

Suppose You have 100 field of data in a table and few data are mandatory and maximum can contain null values. So finally you have to write a sql query where you will find averange percentage number how much filed is filled up with data.

Example: 100 field has 75 data and 25 field don't have any data, are null. So you will find 75% as the result.

SELECT count(*) FROM information_schema.columns WHERE table_name = 'my_table_name'

this query return how much column in my_table_name, but you need to find from a single row.

More real example: All the student have 10 information on a table. Some of the student have no parent name, contact number. So you need to calculate invidual students filled up data percentage and don't count the null filled.

sql postgresql
1 Answers

You can sort of pivot your table into id,col_name,col_value rows. With this structure, calculating non-null percentage would be trivial for a given id, with just count(col_value)/count(*), since count(value) returns count of non-null values, and count(*) returns count of rows.

To obtain such structure, you can build on this excellent answer, and adopt it to your problem like this:

create table test(
  id serial primary key,
  col1 integer,
  col2 integer,
  col3 integer,
  col4 integer,
  col5 integer,
  col6 integer,
  col7 integer,
  col8 integer,
  col9 integer,
  col10 integer
);

insert into test values
  (1,2,2,2,2,2,2,2,2,2,2),
  (2,2,2,null,2,2,2,2,2,null,null),
  (3,2,2,2,2,2,2,2,2,null,null);

select test.id, count(colvalue)::float/count(*) non_null_pct
from test, jsonb_each_text(to_jsonb(test)) as x(colname,colvalue)
where x.colname!='id'
group by test.id;
id non_null_pct
2 0.7
3 0.8
1 1

fiddle

qaziqarta0
Related