Count Number of Not Null Fields Within a Row

Count of non-null columns in each row

select
    T.Column1,
    T.Column2,
    T.Column3,
    T.Column4,
    (
        select count(*)
        from (values (T.Column1), (T.Column2), (T.Column3), (T.Column4)) as v(col)
        where v.col is not null
    ) as Column5
from Table1 as T

Count non-null values in each column of a dataframe in R

With aggregate, use sum of non-NA elements (assuming the missing value is NA) as length returns the total number of elements (per group as we are grouping by group)

aggregate(. ~ group, df, FUN = function(x) sum(!is.na(x)), na.action = NULL)

If the NA value is a string element "N/A"

aggregate(. ~ group, df, FUN = function(x) sum(x != "N/A"), na.action = NULL)
   group cell_a cell_b cell_c
1     A      2      3      2
2     B      2      0      1

data

df <- structure(list(cell_a = c("N/A", "1.2", "3", "N/A", "1.2", "2"
), cell_b = c("2.5", "3.6", "2.1", "N/A", "N/A", "N/A"), cell_c = c("5", 
"N/A", "3.2", "1", "N/A", "N/A"), group = c("A", "A", "A", "B", 
"B", "B")), class = "data.frame", row.names = c(NA, -6L))

Count not-null columns in row

You can do this without spelling out all columns - or even knowing about them - with JSON functions in Postgres 9.3 or later:

SELECT t.*, count(value)::int AS notnull_ct   -- cast to int is optional
FROM   tbl t, json_each_text(row_to_json(t))  -- implicit LATERAL join
-- WHERE key LIKE 'col%' -- optionally consider only selected columns
GROUP  BY tbl_id;        -- PK column

• What is the difference between LATERAL and a subquery in PostgreSQL?

json_each_text() returns (key, value) by default. Use different aliases and / or table-qualify names in case of naming conflicts. If you are only interested in selected columns, you can filter column names in a WHERE clause.

Or use the additional module hstore for the same purpose, available at least since Postgres 8.3:

• Key value pair in PostgreSQL

SELECT t.*, count(v)::int AS notnull_ct
FROM   tbl t, svals(hstore(t)) v
GROUP  BY tbl_id;

The main feature is that count() does not count NULL values (and never returns NULL either). Exactly what you need.

You can encapsulate it in a function. A simple SQL function with a polymorphic input type does the job:

CREATE OR REPLACE FUNCTION f_count_notnull_in_row(ANYELEMENT)
  RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT count(value)::int
 FROM   json_each_text(row_to_json($1))';

Call:

SELECT *, f_count_notnull_in_row(t)
FROM  tbl t;

SQL Fiddle (reusing Bill's setup).

Count non-null values from multiple columns at once without manual entry in SQL

Consider below approach (no knowledge of column names is required at all - with exception of user)

select column, countif(value != 'null') nulls_count
from your_table t,
unnest(array( 
  select as struct trim(arr[offset(0)], '"') column, trim(arr[offset(1)], '"') value
  from unnest(split(trim(to_json_string(t), '{}'))) kv,
  unnest([struct(split(kv, ':') as arr)])
  where trim(arr[offset(0)], '"') != 'user'
)) rec
group by column          

if applied to sample data in your question - output is

count number of columns that not null in a row

In case anyone is interested this is how i ended up doing it

private function complete_percentage($model, $table_name, $resource){
    $pos_info =  DB::select(DB::raw('SHOW COLUMNS FROM '.$table_name));
    $base_columns = count($pos_info);
    $not_null = 0;
    foreach ($pos_info as $col){
        $not_null += app('App\\'.$model)::selectRaw('SUM(CASE WHEN '.$col->Field.' IS NOT NULL THEN 1 ELSE 0 END) AS not_null')->where('user_id', '=', $resource->user_id)->first()->not_null;
    }

    return ($not_null/$base_columns)*100;
}

---

Related Topics