CODEWITHSUNDEEP
oracle-databaseplsqlnull
chris
chris

Reputation: 37460

oracle: counting number of null fields in a row

I have a table that has 5 "optional" fields. I'd like to find out how many rows have all 5 null, how many have 1 field non-null, etc.

I've tried a couple of things, like:

select 
 count(*),
 ( (if field1 is null then 1 else 0) +
   (if field2 is null then 1 else 0) +
 etc.

but of course that doesn't work.

Ideally, I'm looking for output that's something like

Nulls   Cnt
0        200
1        345
...
5        40

Is there an elegant solution?

Upvotes: 1

Views: 1759

Answers (2)

Benoit
Benoit

Reputation: 79185

The keyword is not if, it is case, and you must use end to end the case statement.

Here is a query that can suit you:

WITH subQuery AS
(
  SELECT My_Table.*, (CASE WHEN My_Table.field1 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field2 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field3 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field4 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field5 IS NULL THEN 1 ELSE 0 END ) NumberOfNullFields
    FROM My_Table
)
SELECT NumberOfNullFields, COUNT(*)
  FROM subQuery
 GROUP BY NumberOfNullFields;

Upvotes: 2

Harrison
Harrison

Reputation: 9090

While there is nothing wrong with the case WHEN counting, I just wanted to see if there was another way. 

WITH SAMPLEDATA AS(--just generate some data with nulls for 5 cols
    select  level ,
            (case when mod(level,2) = 0 then 1 else null end) colA,
            (case when mod(level,3) = 0 then 1 else null end) colB,
            (case when mod(level,5) = 0 then 1 else null end) colC,
            (case when mod(level,7) = 0 then 1 else null end) colD,
            (case when mod(level,11) = 0 then 1 else null end) colE

      from dual
      connect by level < 1000
    ), --utilize the count(Aggregate)'s avoidance of nulls to our summation advantage
    nullCols as(
    SELECT COUNT(COLA) aNotNull
           ,cOUNT(*)-COUNT(COLA) aNull
           ,count(colB) bNotNull
           ,cOUNT(*)-count(colB) bNull
           ,count(colc) cNotNull
           ,cOUNT(*)-count(colc) cNull
           ,count(cold) dNotNull
           ,cOUNT(*)-count(cold) dNull
           ,count(cole) eNotNull
           ,cOUNT(*)-count(cole) eNull
           , cOUNT(*) TotalCountOfRows
     from SAMPLEDATA  )
    SELECT (select count(*) from sampledata where cola is null and colb is null and colc is null and cold is null and cole is null) allIsNull     
           ,nullCols.*
    FROM nullCols;

ALLISNULL              ANOTNULL               ANULL                  BNOTNULL               BNULL                  CNOTNULL               CNULL                  DNOTNULL               DNULL                  ENOTNULL               ENULL                  TOTALCOUNTOFROWS       
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
207                    499                    500                    333                    666                    199                    800                    142                    857                    90                     909                    999

this utilizes the

If expression in count(expression) evaluates to null, it is not counted: as noted from here

This method, as is obvious above, does not 'eloquently' sum all null columns well. Just wanted to see if this was possible without the CASE logic.

Upvotes: 1

Related Questions