Counting Null values within a particular row

Question

I have a table that looks like this

Indiv_id     Trip_band    Fav1    Fav2    Fav3    Fav4    Fav5  
1234    0-90    386568    null    null    null    568889  
5678    91-180    null    889546    887456    856234   null

I would like to add another column that counts the amount of null values within a particular row and have my output look like this:

Indiv_id     Trip_band    Fav1    Fav2    Fav3    Fav4    Fav5    null_count  
1234    0-90    386568    null    null    null    568889    3  
5678    91-180    null    889546    887456    856234    null    2

Is this possible to achieve?

Thanks in advance

https://stackoverflow.com/a/8596541/2348125 – Amir Molaei Mar 04 '19 at 17:35 — Amir Molaei, Mar 04 '19 at 17:35

score 1 · Answer 1 · edited Mar 04 '19 at 17:48

1

you can try like below by using case

 select t.*, 
        case when t.col1 is null then 1 
                                 else 0 end 
        + case when t.col2 is null then 1 else 0 end 
        + ........ 
        + case when coln is null then 1 else 0 end as null_count
   from table t

so you need to use your all column name like the upper descriptive way

edited Mar 04 '19 at 17:48

Alex Poole

183,384
11
179
318

answered Mar 04 '19 at 17:35

Zaynul Abadin Tuhin

31,407
5
33
63

1

or, since it is oracle, use NVL instead :) – Jorge Campos Mar 04 '19 at 17:36
1

@JorgeCampos - there is no way to use `nvl` for this. Did you mean `nvl2`? – Mar 04 '19 at 17:41
@JorgeCampos you are right that already mentioned Alex who is boss of oracle :) – Zaynul Abadin Tuhin Mar 04 '19 at 17:41
2

Ha ha, hardly \*8-) Incidentally, I guess you *could* use `nvl()` instead of `else 0` but that just complicates things for no benefit... – Alex Poole Mar 04 '19 at 17:47
@mathguy you are very right sir! I meant nvl2... though technically it still possible with nvl and some hacky stuff :) – Jorge Campos Mar 04 '19 at 17:50

Alex Poole · Answer 2 · 2019-03-04T17:41:46.937

You can add together the result of multiple case expressions, one per column you want to check:

-- CTE for sample data
with your_table (Indiv_id, Trip_band, Fav1, Fav2, Fav3, Fav4, Fav5  ) as (
  select 1234, '0-90', 386568, null, null, null, 568889 from dual
  union all
  select 5678, '91-180', null, 889546, 887456, 856234, null from dual
)
-- actual query
select t.*,
    case when fav1 is null then 1 else 0 end
  + case when fav2 is null then 1 else 0 end
  + case when fav3 is null then 1 else 0 end
  + case when fav4 is null then 1 else 0 end
  + case when fav5 is null then 1 else 0 end
  as null_count
from your_table t;

  INDIV_ID TRIP_B       FAV1       FAV2       FAV3       FAV4       FAV5 NULL_COUNT
---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
      1234 0-90       386568                                      568889          3
      5678 91-180                889546     887456     856234                     2

or you could use the Oracle-specific nvl2() function:

select t.*,
    nvl2(fav1, 0, 1)
  + nvl2(fav2, 0, 1)
  + nvl2(fav3, 0, 1)
  + nvl2(fav4, 0, 1)
  + nvl2(fav5, 0, 1)
  as null_count
from your_table t;

  INDIV_ID TRIP_B       FAV1       FAV2       FAV3       FAV4       FAV5 NULL_COUNT
---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
      1234 0-90       386568                                      568889          3
      5678 91-180                889546     887456     856234                     2

but the case expressions are clearer to me.

There isn't a built-in function that can be applied to all columns in a row at once though, you need to check them all individually. (I guess you could unpivot, count, and pivot back - but that's a lot of extra work and you still need to list the columns anyway...)

score 0 · Answer 3 · answered Mar 04 '19 at 18:54

In Oracle 12C+, you can use a lateral join:

select x.*, c.cnt
from t cross join lateral
     (select count(*) as cnt
      from (select x.fav1 as fav from dual union all
            select x.fav2 from dual union all
            select x.fav3 from dual union all
            select x.fav4 from dual union all
            select x.fav5 from dual union all
           ) x
      where fav is not null
     ) c;

Counting Null values within a particular row

3 Answers3